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 ?
select
     CAST(SERVERPROPERTY(”MachineName”) AS VARCHAR(100)) + ”\” + CAST(SERVERPROPERTY(”InstanceName”) AS VARCHAR(100))
    ,DB_NAME() AS DatabaseName
    ,name
    ,type_desc
    ,CONVERT(decimal(12,2),ROUND(size/128.000,2))
    ,ROUND(fileproperty(name,”SpaceUsed”)/128.000,2)
    ,CONVERT(decimal(12,2),ROUND((size-fileproperty(name,”SpaceUsed”))/128.000,2))
    ,physical_name
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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s