Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

What Is The Size Of The Log In Kilobytes For Each Database In SQL Server 2005 and up

From Wiki

Jump to: navigation, search

What if you want to know the size in KB for each log size and also for all of them combined? In SQL Server 2005 and 2008 you can use the sys.dm_os_performance_counters dynamic management view to find out this information.

Here is the query for that.

  1. SELECT instance_name,cntr_value FROM sys.dm_os_performance_counters
  2. WHERE OBJECT_NAME = 'SQLServer:Databases'  
  3. and counter_name = 'Log File(s) Size (KB)'  
  4. 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 because I ran this query on a QA machine.

Contributed by: --SQLDenis 18:55, 15 April 2010 (GMT)

Part of SQL Server Admin Hacks

716 Rating: 2.7/5 (32 votes cast)