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!
Read more on this article...

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! :)
Read more on this article...