THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Getting Log Space Usage without using DBCC SQLPERF

Every now and then I get a SQL epiphany and figure out another way of doing something.  Forever, I have used DBCC SQLPERF('logspace') to get the utilization information for the database transaction logs in SQL Server.  A question on the forums asked how to get the code for this DBCC command because sp_help_text wouldn't provide it.  While you can get the code for a number of system stored procedures, you can't get the code for DBCC commands and Extended Stored Procedures because they are compiled unmanaged code, not TSQL.

Until today, my response has been, DBCC SQLPERF('logspace') is the only way to get what you are after.  However, for whatever reason, my brain clicked two different events together and made a connection.  This week while doing some consulting work, I was asked about logs filling faster than backups were scheduled and how to work to prevent the logs from filling completely.  The solution which I will blog about in another post is to use a SQL Agent Alert on the Percent Log User Performance Counter that fires a SQL Agent Job to performs a Log Backup of the Database on the server.  Since we can read that performance counter from SQL Server using the sys.dm_os_performance_counters DMV, then we can also use the same DMV to get the log space information for all of our databases:

SELECT instance_name AS DatabaseName,
      
[Data File(s) Size (KB)],
      
[LOG File(s) Size (KB)],
      
[Log File(s) Used Size (KB)],
      
[Percent Log Used]
FROM
(
  
SELECT *
  
FROM sys.dm_os_performance_counters
  
WHERE counter_name IN
  
(
      
'Data File(s) Size (KB)',
      
'Log File(s) Size (KB)',
      
'Log File(s) Used Size (KB)',
      
'Percent Log Used'
  
)
     AND
instance_name != '_Total'
) AS Src
PIVOT
(
  
MAX(cntr_value)
  
FOR counter_name IN
  
(
      
[Data File(s) Size (KB)],
      
[LOG File(s) Size (KB)],
      
[Log File(s) Used Size (KB)],
      
[Percent Log Used]
  
)
)
AS pvt

No sooner had I posted this solution than I got the response that this has to work for SQL 2000 as well.  So I had to boot up my SQL 2000 instance on my laptop to actually see how to work that out, and the solution isn't that much different.  The sysperfinfo system view can be used against SQL Server 2000,  2005, and 2008 to get the information using an older "pivot" methodology as follows:

SELECT instance_name AS 'Database Name',
  
MAX(CASE
          
WHEN counter_name = 'Data File(s) Size (KB)'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Data File(s) Size (KB)',
  
MAX(CASE
          
WHEN counter_name = 'Log File(s) Size (KB)'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Log File(s) Size (KB)',
  
MAX(CASE
          
WHEN counter_name = 'Log File(s) Used Size (KB)'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Log File(s) Used Size (KB)',
  
MAX(CASE
          
WHEN counter_name = 'Percent Log Used'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Percent Log Used'
FROM sysperfinfo
WHERE counter_name IN
  
(
      
'Data File(s) Size (KB)',
      
'Log File(s) Size (KB)',
      
'Log File(s) Used Size (KB)',
      
'Percent Log Used'
  
)
  AND
instance_name != '_total'
GROUP BY instance_name

As a warning, the sysperfinfo view is a deprecated feature in 2005 and 2008 and is only carried forward for backwards code compatibility.  You can expect that it will be removed in a future version of SQL so if you have 2005 installed, it is better to use the DMV's for this.

Published Saturday, June 06, 2009 2:10 AM by Jonathan Kehayias

Comments

 

Carl Feder said:

Unfortunately, both SQLPERF and the Data Mangement view report a non-zero value for log space used when zero is a more appropriate value.  You can see this by creating  a new database and then immediately running either solution which produces:                                                              

2240 for Data File(s) Size (KB)

 552 for LOG File(s) Size (KB)

 219 for Log File(s) Used Size (KB)

   39 for Percent Log Used

The below procedure returns zero log used space when one and only one virtual log file is in use and there are no open transactions for the database.  The SP works for SQL Server 7 to 2008.

CREATE PROCEDURE dbo.sp_DatabaseLog_list_SpaceUsed

AS

SET NOCOUNT ON

SET XACT_ABORT ON

IF OBJECT_ID('tempdb..#loginfo') IS NOT NULL DROP TABLE #loginfo

CREATE TABLE #loginfo  

( DatabaseName sysname DEFAULT(DB_NAME() )

, FileId BIGINT

, FileSize BIGINT

, StartOffset BIGINT

, FSeqNo   BIGINT

, Status INT

, Parity SMALLINT

, CreatedLSN NUMERIC(38) )

DECLARE @sqlcmd NVARCHAR(4000)

, @ByteMb DECIMAL(12,1)

SET @ByteMb = 1024 * 1024

SET @sqlcmd =

'use [?]

insert into #loginfo

(FileId,FileSize , StartOffset, FSeqNo , Status, Parity, CreatedLSN)

EXEC (''DBCC LOGINFO'')'

EXEC sp_MsForEachDb @sqlcmd

CREATE TABLE #OpenTran

( DatabaseName sysname DEFAULT(DB_NAME() )

, Defn VARCHAR(25)

, Details SQL_VARIANT

)

SET @sqlcmd =

'use [?]

insert into #OpenTran

(Defn , Details )

EXEC (''DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS'')'

EXEC sp_MsForEachDb @sqlcmd

SELECT @@SERVERNAME AS SQLServerName

, LogInfo.DatabaseName

, LogSizeMb

, CASE WHEN VirtualLogActiveCnt > 1 THEN LogUsedMb

WHEN OpenTran.DatabaseName IS NOT NULL THEN LogUsedMb

ELSE 0

END AS LogUsedMb

, CASE WHEN VirtualLogActiveCnt > 1 THEN LogUsedMb

WHEN OpenTran.DatabaseName IS NOT NULL THEN LogUsedMb

ELSE 0

END * 100 / LogSizeMb AS LogUsedPct

, VirtualLogCnt

, VirtualLogActiveCnt

, CASE WHEN OpenTran.DatabaseName IS NOT NULL THEN 'Y'

ELSE 'N'

END AS DatabaseOpenTranInd

FROM (

SELECT DatabaseName

, SUM(FileSize ) / @ByteMb AS LogSizeMb

, SUM(CASE STATUS

WHEN 2 THEN FileSize

ELSE 0.0

END) / @ByteMb AS LogUsedMb

, COUNT(*) AS VirtualLogCnt

, SUM(CASE STATUS

WHEN 2 THEN 1

ELSE 0

END) AS VirtualLogActiveCnt

FROM #loginfo

GROUP BY DatabaseName

) AS LogInfo

LEFT OUTER JOIN

(SELECT DISTINCT DatabaseName

FROM #OpenTran

) AS OpenTran

ON  OpenTran.DatabaseName = LogInfo.DatabaseName

RETURN 0

GO

June 6, 2009 7:56 AM
 

Uri Dimant said:

Another one could be

SELECT SUM([Log Record Length]) /1024.0 AS Mb

FROM ::fn_dblog(null, null)

June 8, 2009 12:56 AM
 

Glenn Berry said:

Nice query Jonathan. I like this one for getting similiar information about one specific database:

-- Individual File Size query

SELECT name AS 'File Name' , file_id, physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *

FROM sys.database_files;

June 8, 2009 2:16 PM
 

Jonathan Kehayias said:

Carl,

Wow. I never paid that much attention, but that certainly makes a difference.  You are correct in that SIMPLE databases report invalid numbers using the performance counters unless I missed doing a division by a base counter.  I'll have to double check that and get back to this.

June 8, 2009 3:02 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement