Getting Log Space Usage without using DBCC SQLPERF

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

Another one could be

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

FROM ::fn_dblog(null, null)

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;