1. Skip to Menu
  2. Skip to Content
  3. Skip to Footer>

Expert Sessions!

Script to monitor Transaction Log Space and Alert

Tuesday, 31 July 2012 11:28

Written by Prakash Palani

Print E-mail

Here is a script that can help you to monitor the used transaction log space and alert by email when it reaches the threshold value.

 

Step 1 : Create a stored procedure that will be called from Step 2 to send out the alert email

 

USE msdb
GO
CREATE  procedure Mail_Used_TrLog_PCT_AN_Basis
as
Declare @Subject varchar(4000)
DECLARE @To varchar(4000)

SELECT  @To = ' This e-mail address is being protected from spambots. You need JavaScript enabled to view it '
SELECT @Subject='SQL Alert: ' + DbName + ' ----> ' + left(LogPerc,4) + '% of Tr Log Space Used' FROM msdb..TRLOGRESULT 
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'BasisSupport',
@recipients = @To,
@subject = @Subject

 

Step 2 : Create the below script to monitor the trlog space and invoke stored procedure mentioned in step to send out the alerts

drop table msdb..TRLOGSP
create table msdb..TRLOGSP
(
DbName varchar(20),
LogSizeMB varchar(20),
LogPerc real,
Status int
)

insert msdb..TRLOGSP EXEC('DBCC SQLPERF (LOGSPACE)') select LogPerc from msdb..TRLOGSP where DbName='DUM'
if (select LogPerc from msdb..TRLOGSP where DbName='DUM') >= 70
begin
create table msdb..TRLOGRESULT
(
DbName varchar(20),
LogSizeMB varchar(20),
LogPerc real,
Status int
)
insert into msdb..TRLOGRESULT(DbName, LogSizeMB, LogPerc, Status) (select DbName, LogSizeMB, LogPerc, Status from msdb..TRLOGSP where DbName='DUM')
EXEC msdb..Mail_Used_TrLog_PCT_AN_Basis
drop table msdb..TRLOGRESULT
end
else
begin
select * from msdb..TRLOGSP
end

 

In order to email functionality/email stored procedure to work, you need to perform the mail configuration  which I have described under http://basisondemand.com/mss/script_to_monitor_transaction_log_space_and_alert_dbcc_sqlperflogspace

 

{fcomment} {flike}

Share

 

Script to monitor Transaction Log Space and Alert