SQL Server Log Growth Alerts – How to Stop Random Alerts

alertstransaction-log

I've set up alerting on all my database servers and every once in awhile I'll get the following error, sometimes it goes on and on and on, other times it'll be a few times here and there. 

The SQL Server performance counter 'Log Growths' (instance '_Total') of object 'SQLServer:Databases' now equals the threshold of 1.00.

Are there any configurations that I can change to stop this? It only appears to be coming from two servers.

Best Answer

This is telling you that a or several transaction logs are growing repeatedly, which means it is set to FULL or BULK LOGGED recovery mode with no or insufficient transaction log backups, or someone has written a really bad or long query that is keeping the transaction log active.

You should look for all FULL recovery model databases on that server and when was the last time a transaction log backup took place first, as that's the most dangerous. The tlog could grow to where it takes up all the disk stopping all writes to that DB or any other DB with data or logs on that disk assuming there isn't a max size set. If there isn't, moving fwd, you'll probably want to set a max size so 1 DB doesn't take down all DBs tlogs.

SELECT name, recovery_model_desc 
FROM sys.databases

See any that are full? If so, when was the last transaction log backup? Here is a script to help you on that:

SELECT   d.name,
         d.recovery_model_desc,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name, d.recovery_model_desc
ORDER BY backup_finish_date DES

If that doesn't give you the info you need here is a way to find the longest running top 10 transactions from msdn:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

Finally once you see which DB has growing log files you can see what is holding the log file open. It might even be something like replication setup or other HA solutions that weren't fully removed. This will tell you:

SELECT [log_reuse_wait_desc]
    FROM [master].[sys].[databases]
--  WHERE [name] = N'DBNAME';
GO
Related Question