CODEWITHSUNDEEP
sqldatabaset-sqlsql-server-2008-r2
muddu83
muddu83

Reputation: 495

Script to list the SQL Server Databases, Size and Utilisation by Specific Application or Service

I am not sure if its possible, but would really appreciate any assistance.

I am looking for a script which can generate the complete list of SQL Server databases with the following details for each database of a SQL Server instance:

Upvotes: 11

Views: 72270

Answers (5)

GerardV
GerardV

Reputation: 384

The answer(s) provided here return the size of the database files, which is not necessarily the size of the data in the database. To get the size of data in each database on a SQL Server, use the following query.

DECLARE @r TABLE( name SYSNAME, size NUMERIC(36, 2) )
DECLARE @db SYSNAME
    ,   @sql NVARCHAR(MAX)
    ,   @size NUMERIC(36,2)
--
SELECT  @db = MIN( name ) FROM sys.databases
--
WHILE @db IS NOT NULL
BEGIN
    SET @sql = N'SELECT @size = CAST( ROUND( ( SUM( a.used_pages ) * 8 / 1024.00 ), 2) AS NUMERIC( 36, 2) )
    FROM    ' + @db + '.sys.tables t
    LEFT JOIN   ' + @db + '.sys.indexes i
        ON  t.OBJECT_ID = i.object_id
    LEFT JOIN   ' + @db + '.sys.partitions p 
        ON  i.object_id = p.OBJECT_ID
        AND i.index_id = p.index_id
    JOIN    ' + @db + '.sys.allocation_units a 
        ON  p.partition_id = a.container_id'
--
    EXEC sp_executesql @sql, N'@size NUMERIC(36,2) OUTPUT', @size OUTPUT
--
    INSERT INTO @r
    SELECT @db, @size
--  
    SELECT @db = MIN( name ) FROM sys.databases WHERE name > @db    
END
--
SELECT name, size AS [size (MB)] FROM @r ORDER BY 2 DESC

Upvotes: 0

Fran Ambrose
Fran Ambrose

Reputation: 11

Here's a variation on the query above.


    SELECT
        D.Name as [Database Name],
        F.state_desc AS OnlineStatus,
        convert(varchar(12),CAST((sum(
        case when f.physical_name like '%ldf'then 0 else f.size end)*8.0)/1024.0/1024.0 AS numeric(8,2))) AS [Data File Size GB],
        convert(varchar(12),CAST((sum(
        case when f.physical_name like '%ldf'then f.size else 0 end)*8.0)/1024.0/1024.0 AS numeric(8,2))) AS [Log File Size GB]
        --,CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' as SizeInBytes
    FROM 
        sys.master_files F
        INNER JOIN sys.databases D ON D.database_id = F.database_id
    group by D.name,F.state_desc
    ORDER BY
        D.name

Upvotes: 1

Chris Halcrow
Chris Halcrow

Reputation: 31940

Using EXEC sp_databases may show the wrong sizes for the DB. Here's a nice, reliable query that will give all database names, sizes and statuses, although not which apps are utilising the DBs:

SELECT
    D.name,
    F.Name AS FileType,
    F.physical_name AS PhysicalFile,
    F.state_desc AS OnlineStatus,
    CAST((F.size*8)/1024 AS VARCHAR(26)) + ' MB' AS FileSize,
    CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' as SizeInBytes
FROM 
    sys.master_files F
    INNER JOIN sys.databases D ON D.database_id = F.database_id
ORDER BY
    D.name

Upvotes: 26

David Brabant
David Brabant

Reputation: 43459

You could use Nagios for your various monitoring tasks. Nagios provides complete monitoring of MSSQL - including availability, database and table sizes, cache ratios, and other key metrics.

Upvotes: 1

jfoliveira
jfoliveira

Reputation: 2168

To get only general information (database name, files and size) you can have some success running the "sp_databases" stored procedure:

exec sp_databases

If the above didn't work in SQL Server 2000, you can try the following:

select *
from sys.sysdatabases

But you can get a more detailed trace and audit data using the "SQL Profiler" that is shipped with SQLServer.

Upvotes: 6

Related Questions