Problem Description:

Many a times there are requirements to monitor SQL server log usage historically and analyze when it is needed. Below are the step by step solutions.

Solution:

Step1: create database

create database MonitorDB
go

Step 2:- Create Table "TlogUsage" in MonitorDB Database

create table dbo.TlogUsage ( ID int identity(1, 1) ,DatabaseName SYSNAME ,LogSizeInMB DECIMAL(21, 5) ,"LogSpaceUsed%" DECIMAL(21, 5) ,Status int ,TimeStamp datetime DEFAULT getdate() )

Step 3:- Create procedure in MonitorDB which will capture dbcc sqlperf(logspace) output and put it in above created table.

Use MonitorDB
go

create procedure MonTlogUsage
as
begin
INSERT INTO dbo.TlogUsage
(
DatabaseName
,LogSizeInMB
,"LogSpaceUsed%"
,Status
)
exec ('dbcc sqlperf(logspace)')
end
go

Step 4:- Create SQL agent job or any other scheduling tool and put below SQL details to capture log usage details.

Use MonitorDB
go
exec MonTlogUsage
go

Step 5:- Below query will be used to further analyze captured log usage details.

select ID
, DatabaseName
, LogSizeInMB
, "LogSpaceUsed%"
, TimeStamp
from TlogUsage
where (DatabaseName = 'MonitorDB')
order by TimeStamp desc
go