Sometimes you want to quickly see the percentage of log spaced that each database is using on your server. In SQL Server 2005 and 2008 you can use the sys.dm_os_performance_counters dynamic management view to find out this information. The query below will list all database and the percentage of log spaced used. The cntr_value column will have the percent of the log file that is being used and instance_name will be the database name.

select instance_name,cntr_value from sys.dm_os_performance_counters
where Object_name = 'SQLServer:Databases'   
and counter_name = 'Percent Log Used' 
and instance_name <> '_Total'                                                                                                                          
order by   cntr_value desc  

Here is the output from that query.

instance_name       cntr_value
model           90
ReportServerTempDB  66
master              65
mssqlsystemresource 55
ReportServer        55
tempdb              50
msdb                5

Notice that I filtered out the total with this clause _and instance_name <> ‘Total’ The total number doesn’t really make sense for that query.

Now let’s take a look at another query. What if I want to know the size in KB for each log size and also for all of them combined? Here is the query for that.

select instance_name,cntr_value from sys.dm_os_performance_counters
where Object_name = 'SQLServer:Databases'   
and counter_name = 'Log File(s) Size (KB)'   
order by   cntr_value desc 

Here is the output from that query.

instance_name      cntr_value
_Total          37524936
iSource_Report      14539576
iSource_Distribution    13217784
DJHFI_Research_db   8207096
msdb            625784
tempdb          102136
master          2808
ReportServer        1016
model           1016
ReportServerTempDB  760
mssqlsystemresource 504

As you can see _Total is the first thing listed and it is actually a sum of all the log files in the query. The numbers don’t add up in my output because I removed some database names after running the query.

Since I showed you how to do the log files, here is a way how to show the size of all the data files. here is the query for the data files

select instance_name,cntr_value from sys.dm_os_performance_counters
where Object_name = 'SQLServer:Databases'   
and counter_name = 'Data File(s) Size (KB)'   
order by   cntr_value desc 

And here is the output

instance_name      cntr_value
_Total          58232704
msdb                    13512512
tempdb                  1542720
mssqlsystemresource     39232
master                  6720
ReportServer            4288
ReportServerTempDB      3264
model                   2240

And just as with the log size query, you can see _Total is the first thing listed and it is actually a sum of all the data files in the query. The numbers don’t add up in my output because I removed some database names after running the query.

I will be back with another post tomorrow showing you how you can use the sys.dm_os_performance_counters dynamic management view to see if you are still using any deprecated features in your database.

*** Remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum