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

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!