This SP will monitor Log space for SQL Server.
IF OBJECT_ID(‘dbo.logspace_monitor’, ‘P’)
IS NOT NULL
DROP PROCEDURE dbo.logspace_monitor
GO
CREATE PROCEDURE logspace_monitor
@threshold
int = 70
AS
SET NOCOUNT ON
CREATE TABLE #logSpaceStats
(
databaseName sysname,
logSize decimal(18,5),
logUsed decimal(18,5),
staus int
)
declare @cmd nvarchar(max)
set @cmd = ‘dbcc sqlperf(logspace) with no_infomsgs’
insert into #logSpaceStats exec sp_executesql @cmd
PRINT ‘*********************************************’
PRINT ‘DATABASES with logspace used greated than 70%’
PRINT ‘*********************************************’
PRINT ”
select
cast(DatabaseName as varchar(25))as ‘DATABASE NAME’,
cast(logused as varchar(20))as ‘LOG SPACE USED’,
cast(a.log_reuse_wait_desc as varchar(15)) as ‘LOG REUSE WAIT’
from #logSpaceStats,sys.databases a where databasename=a.name and logUsed >@threshold
go