XML FeedsSometimes 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
For instances I don't have licensing for my monitoring software I use a query very much like yours and it works great.
Thanks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.