System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command - Microsoft SQL Server 2012 Performance Tuning - page 71

--Retriving log space details for
-- a specific databases.
SELECT
DatabaseName
,LogSize AS LogSizeInMB
,LogSpaceUsed As LogspaceUsed_In_Percent
,Status
FROM dbo.#tbl_DBLogSpaceUsage
WHERE DatabaseName = 'AdventureWorks2012'
GO
How it works...
In this example, we created a temporary table named #tbl_DBLogSpaceUsage . We first
checked if the table exists, with the IF condition. If one exists, we first drop the table. The
table structure that we have created should be identical to the set of columns that DBCC
SQLPERF returns.
Next, DBCC SQLPERF is executed and its output is collected in the #tbl_DBLogSpaceUsage
table by INSERT…EXECUTE statements. Note that we have used a dynamic SQL statement
here in the EXECUTE statement, as we cannot directly redirect the output of the DBCC
command into the table.
Next, we queried the table and retrieved the log space usage statistics information
for all databases. The very next query returns the log space usage statistics for the
AdventureWorks2012 database by creating a filter on the DatabaseName column.
From the output, you can know the size of the log for every database and if you see any
alarming statistics, you can immediately take the necessary steps, such as backing up
the log files.
There's more...
You can use the logic of the script provided in this recipe to accumulate the log space usage
statistics for all databases and populate these details in a table. Over time, the table becomes
a history of log space usage details, which you can use in trend analysis of how fast a log file
is growing.