Send Email To DBAs When Transaction Log Sizes Reachs the Certain Limit

dbtut

6 years ago

Sometimes the transaction log file may grow more than normal for some reasons. For example when the transaction logs are not backed up. In the following articles you can find articles about why the log file can be full or grows more than normal.

“SQL Server Does Not Truncate the Transaction Log After Log Backup and Can Not Shrink the Transaction Log“,

“Transaction Log is Full Due To a Reason(ACTIVE_TRANSACTION, LOG_BACKUP, REPLICATION)”

If you know in advance that the log file is full and there is a problem, you can create a solution before it is too late. So we prepared the following script for our environment and wanted to share it with you.

Below you can find the script that send email transaction log file informations which log files are over 500 GB(You can change this value in the script if you want) in size and are too full to shrink.

You must configure database mail so that your SQL Server can send mail. The article “How To Configure Database Mail On SQL Server” can help you.

DECLARE @DatabaseName VARCHAR(250)
DECLARE @LogSize2 DECIMAL(19,2)
DECLARE @UsedLogSize2 DECIMAL(19,2)
DECLARE @HTML  NVARCHAR(MAX) 
DECLARE @Sql NVARCHAR(MAX) 
DECLARE @Counter tinyint
SET @HTML =
    N'<H1>Transaction Log Files That Should Be Checked</H1>' +  
    N'<table border="1">' +  
    N'<tr><td>Database Name</td><td>Log Size</td><td>Used Log Size</td></tr>' ;
	SET @Counter=0
DECLARE LogAlertCrs CURSOR

FOR
SELECT '['+name+']' FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(dr_state.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id
and dr_state.replica_id = ar_state.replica_id
where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1
)

OPEN LogAlertCrs
FETCH NEXT FROM LogAlertCrs INTO @DatabaseName
WHILE @@FETCH_STATUS =0
BEGIN 

Select @Sql = 'Use ' +@DatabaseName + N';


         SELECT 
@LogSize=CAST(sum(size)/131072.0 AS decimal(19,2)) ,
@UsedLogSize=CAST(sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/131072.0 AS DECIMAL(19,2)) from sys.database_files  
where type=1 group by type'

EXEC sp_executesql @Sql,@Params= N'@LogSize DECIMAL(19,2) OUTPUT, @UsedLogSize DECIMAL(19,2) OUTPUT'
,@LogSize = @LogSize2 OUTPUT
,@UsedLogSize = @UsedLogSize2 OUTPUT;  

 

IF(@LogSize2>=500 AND @LogSize2-@UsedLogSize2<1)
BEGIN

SET @Counter+=1;
	SET @HTML+=N'<tr><td>'+@DatabaseName+'</td><td>'+CAST(@LogSize2 AS nvarchar(50))+
	'</td><td>'+CAST(@UsedLogSize2 AS nvarchar(50))+'</td></tr>';
	--print @html;

END

 FETCH NEXT FROM LogAlertCrs INTO @DatabaseName
  
END       
SET @HTML+=N'</table>' ;  
if(@Counter>0)
BEGIN       
EXEC msdb.dbo.sp_send_dbmail
@recipients='nurullah.cakir@saglik.gov.tr',
@subject = 'Check Transaction Log File Sizes',
@body = @HTML,
@body_format = 'HTML' ;
END
CLOSE LogAlertCrs
DEALLOCATE LogAlertCrs

DECLARE @DatabaseName VARCHAR(250)

DECLARE @LogSize2 DECIMAL(19,2)

DECLARE @UsedLogSize2 DECIMAL(19,2)

DECLARE @HTML NVARCHAR(MAX)

DECLARE @Sql NVARCHAR(MAX)

DECLARE @Counter tinyint

SET @HTML =

N'<H1>Transaction Log Files That Should Be Checked</H1>' +

N'<table border="1">' +

N'<tr><td>Database Name</td><td>Log Size</td><td>Used Log Size</td></tr>' ;

SET @Counter=0

DECLARE LogAlertCrs CURSOR

FOR

SELECT '['+name+']' FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(dr_state.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)

JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id

and dr_state.replica_id = ar_state.replica_id

where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1

)

OPEN LogAlertCrs

FETCH NEXT FROM LogAlertCrs INTO @DatabaseName

WHILE @@FETCH_STATUS =0

BEGIN

Select @Sql = 'Use ' +@DatabaseName + N';

SELECT

@LogSize=CAST(sum(size)/131072.0 AS decimal(19,2)) ,

@UsedLogSize=CAST(sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/131072.0 AS DECIMAL(19,2)) from sys.database_files

where type=1 group by type'

EXEC sp_executesql @Sql,@Params= N'@LogSize DECIMAL(19,2) OUTPUT, @UsedLogSize DECIMAL(19,2) OUTPUT'

,@LogSize = @LogSize2 OUTPUT

,@UsedLogSize = @UsedLogSize2 OUTPUT;

IF(@LogSize2>=500 AND @LogSize2-@UsedLogSize2<1)

BEGIN

SET @Counter+=1;

SET @HTML+=N'<tr><td>'+@DatabaseName+'</td><td>'+CAST(@LogSize2 AS nvarchar(50))+

'</td><td>'+CAST(@UsedLogSize2 AS nvarchar(50))+'</td></tr>';

--print @html;

END

FETCH NEXT FROM LogAlertCrs INTO @DatabaseName

END

SET @HTML+=N'</table>' ;

if(@Counter>0)

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@recipients='nurullah.cakir@saglik.gov.tr',

@subject = 'Check Transaction Log File Sizes',

@body = @HTML,

@body_format = 'HTML' ;

END

CLOSE LogAlertCrs

DEALLOCATE LogAlertCrs