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.)

Solution 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

Solution 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.

Solution 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.