Wednesday, March 14, 2012

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! :)
[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

2 comments:

Jack Corbett said...

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

Artemakis Artemiou [MVP] said...

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!

Cheers