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:


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
( dbname VARCHAR(250),
logsize FLOAT,
logspaceused FLOAT,
status INT

–Step 3: Populate temporary table with log size information

–Step 4: Process the temporary table
FROM #tempTbl

FROM #tempTbl
ORDER BY logspaceused ASC

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

2 thoughts on “Retrieving Log Space Information within a SQL Server Instance”

  1. I prefer to use sys.master_files and FILEPROPERTY([file_name], 'spaceused') to get the space used. Yes, you need to convert from pages to MB, but that's an easy conversion.

    Just goes to show that there is always more than 1 way to do things in SQL Server

  2. Hi Jack,

    Thank you for your comment.

    Yes, of course! That's one of the things I really like in SQL Server; it allows you to do the same thing using different ways! This is what I call flexibility!


Comments are closed.