Retrieving Log Space Information within a SQL Server Instance

In the everyday life of a Database Administrator there is the task of maintaining the database logs in terms of size they occupy on the disk. Of course, there are automated maintenance and reporting procedures for this task as well as for many other tasks but it is not few the cases where the DBA needs to manually maintain a SQL Server instance!

The fastest way to get log space information for all the databases under a SQL Server instance is to use the following T-SQL command:

DBCC SQLPERF(LOGSPACE)

The above command returns a record for each database under the current SQL Server instance which contains the following information:
– Database Name
– Log Size (MB)
– log Space Used (%)
– Status

Additionally, if you like to process this information, you can do so by first storing it into a temporary table.
You can do this as follows:

–Step 1: If temporary table exists, then drop it
IF Object_id(N’tempdb..#tempTbl’) IS NOT NULL
DROP TABLE #temptbl


–Step 2: Create temporary table
CREATE TABLE #temptbl
( dbname VARCHAR(250),
logsize FLOAT,
logspaceused FLOAT,
status INT
)


–Step 3: Populate temporary table with log size information
INSERT INTO #tempTbl
EXEC(‘DBCC SQLPERF(LOGSPACE)’)


–Step 4: Process the temporary table
–Examples:
SELECT *
FROM #tempTbl
ORDER BY logsize DESC


SELECT *
FROM #tempTbl
ORDER BY logspaceused ASC

I hope you enjoyed the post as much as I did writing it! 🙂