Retrieving Log Space Information within a SQL Server Instance – The Stored Procedure!

Retrieving Log Space Information within a SQL Server Instance – The Stored Procedure!

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
In yesterday’s post, we saw how we can retrieve log space information for all the databases within a SQL Server instance using the command DBCC sqlperf(logspace).

Today we will refine the process even more by creating a stored procedure that returns log space information for a given database!

–******************************
–CREATING THE STORED PROCEDURE
–******************************

–Select the database in which the stored
–procedure will be created
USE [spDBName]
GO


–Check if the stored procedure exists
–and if so drop it
IF OBJECT_ID(‘spDBLogInfo’) IS NOT NULL
DROP PROCEDURE spDBLogInfo
GO


–Create the stored procedure.
–The only input parameter will be the database name.
CREATE PROCEDURE spDBLogInfo
@DBname NVARCHAR (250)
AS
BEGIN
SET nocount ON;



— Main logic

–Step 1: Create temporary table
CREATE TABLE #temptbl
(
DBName NVARCHAR(250),
logsize FLOAT,
logspaceused FLOAT,
status INT
)


–Step 2: Populate temporary table with log size information
INSERT INTO #temptbl
EXEC(‘DBCC sqlperf(logspace)’)


–Step 3: Process the temporary table
SELECT DBName,
ROUND(logsize,2) as ‘Log Size (MB)’,
ROUND(logspaceused,2) as ‘Log Space Used (%)’,
ROUND((logsize-(logsize*(logspaceused/100))),2) as ‘Available Log Space (MB)’
FROM #temptbl
WHERE dbname=@DBname
END
GO
——————————-

Now, let’s say we want to see log space information about the database ‘temp’.
The only we need to do is this:

EXEC spDBName..spDBLogInfo ‘temp’

…and this is what we get:

Feel free to use the stored procedure for your database administration needs!

Cheers!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.