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

Question

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:

• Name
• Sizes
• Utilisation by specific application or service
• Hardware Utilisation (CPU, memory, I/O, etc.)

Ellanellard

You might get a better answer on the DBA site, but I would seriously consider buying a tool that already does all this for you. There are plenty of monitoring solutions available for SQL Server. – Hanky

Answer 1

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

Irrefragable

Shouldn't you divide by 1048576 instead of 1024 to get MB? – Siberson

the text ' Bytes' should be ' KB', I think – Mundane

Answer 2

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.

Rani

Thanks, Jef! But I am looking to build a list of the current SQL Server utilization in terms of number of databases, their present sizes, utilisation by specific application or service, and location by server or cluster. – Ellanellard

Answer 3

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.

Sagittate

Thanks, David but I am not sure if we can have any monitoring tools beyond SCOM. – Ellanellard

Answer 4

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

Selfregulated

Answer 5

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

Ro