Display Transaction Log Size

Many times I have to perform ad-hoc queries that insert, update or delete very large amounts of data.  For example, a nightly ETL load failed and I must reload the data  manually or deleting large amounts of data
in batches from a logging table.  In such cases I like to keep an eye out on how full the transaction log is becoming while these  ad-hoc queries are running to ensure that the T-Log doesn't grow out of control. If needed, I can then pause whatever query is running to allow log truncation to occur or take a manual log backup.

The DBCC SQLPERF(logspace) is a nice way to see how full the transaction log is. But, if you have many databases on the server, it can be diffcult to find the database you're interested in since the list isn't sorted (it's actually sorted by database_id, which isn't terribly helpful). Here is a stored proc that I use that will execute the DBCC command and return the database list sorted alphabetically. You can also pass in a search string for the database name and it will return only those databases that have the search string in their name (sorted by name). I put this proc in a utility database that I use for functions and procs that are needed server-wide.

Usage is very simple:
EXEC dbo.TLogSqlPerf
This will return all databases sorted  alphabetically.

EXEC dbo.TLogSqlPerf @p_DbName = 'Adventure'
This will return only those databases with "Adventure" in their name (i.e.  AdventureWorks2012 and AdventureWorksDW2012)


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'dbo.TLogSqlPerf', N'P') IS NOT NULL DROP PROCEDURE dbo.TLogSqlPerf;
GO

CREATE PROCEDURE dbo.TLogSqlPerf
(
    @p_DbName    NVARCHAR(200) = ''
)
AS

SET NOCOUNT ON;

DECLARE @t TABLE
(
    DatabaseName    NVARCHAR(128),
    LogSizeMB    DECIMAL(18,5),
    LogUsedPct    DECIMAL(18,5),
    [Status]    INT
)
INSERT INTO @t
(
    DatabaseName,
    LogSizeMB,
    LogUsedPct,
    [Status]
)
EXEC sp_executesql N'DBCC SQLPERF(logspace) WITH NO_INFOMSGS'

SELECT
    DatabaseName,
    LogSizeMB,
    LogUsedPct,
    [Status]
FROM @t
WHERE DatabaseName LIKE '%' + @p_DbName + '%'
ORDER BY DatabaseName

RETURN 0;

SET NOCOUNT OFF;