SQL Server - Azure - DotNet - Programming Courses and More - SQLNetHub

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

by

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).

 

Creating the Stored Procedure

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
-------------------------------

 

Using the Stored Procedure

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:

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

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

Learn More Tips like this – Enroll to the Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 2,038