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.
“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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | 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 @subject = 'Check Transaction Log File Sizes', @body = @HTML, @body_format = 'HTML' ; END CLOSE LogAlertCrs DEALLOCATE LogAlertCrs |