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.
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
This is no error. It is just a log file which was too small and has auto growth enabled. It means that while there are transactions running the sql server process has to expand the log file. Expanding the log file is a single threaded process and every transaction which has to write something in the transaction log has to wait. And no: no IFI - you have to wait while the sql server is writing all the zeros onto disk.
Luckily you can query the standard traces about all shrink and growth events. Modified from sqlblog.com for log shrink/growth events:
DECLARE @filename VARCHAR(MAX)
SELECT @filename = CAST(value AS VARCHAR(MAX))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
AND value IS NOT NULL
SELECT gt.EventClass,
te.Name AS EventName,
gt.TEXTData,
gt.NTUserName,
gt.NTDomainName,
gt.HostName,
gt.ApplicationName,
gt.LoginName,
gt.SPID,
gt.StartTime,
gt.EndTime,
gt.ObjectName,
gt.DatabaseName,
gt.FileName
FROM [fn_trace_gettable](@fileName, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass In (92, 95)
ORDER BY StartTime;
