Querying multiple instances for database file information

Today I was assigned a task to query multiple instances and gather space usage details on all databases. First I thought it would be quite easy task to do, but as my final goal was to summarize the statistics per database and not per database file, I had to modify the standard query (which turned out to be one of the most popular suggestions Google and Bing provided). Anyway, if you want to query multiple instances and gather space usage statistics on database and file level, there is the script:

declare @temp table (
     instance_name varchar(100)
    ,database_name varchar(100)
    ,database_file varchar(100)
    ,file_type varchar(5)
    ,all_space decimal(12,2)
    ,space_used decimal(12,2)
    ,space_free decimal(12,2)
    ,physical_path varchar(256)
insert into @temp
exec sp_MSforeachdb ‘
use ?
     CAST(SERVERPROPERTY(”MachineName”) AS VARCHAR(100)) + ”” + CAST(SERVERPROPERTY(”InstanceName”) AS VARCHAR(100))
    ,DB_NAME() AS DatabaseName
from sys.database_files

select * from @temp

The @temp table is used so I can easily copy the results to Excel for example as sp_MSforeachdb returns separate blocks in the result screen. Also based on that table you can summarize database size info if you do not feel confident in doing it with excel.

The script can be run on an instance level or on a Registered Server Group level (for SQL Server 2008 – querying multiple instances).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.