Thursday, March 15, 2012

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!
[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)

0 comments: