2

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.

  • Well you have an alert firing based on how you configured it, so what action is taken to resolve the issue? If this is not an issue then why alert on it? – user507 Aug 23 '15 at 19:48
  • @Shawn - This isn't a specific alert for this specific action. These are general alerts configured by suggestions from Brent Ozar's scripts. – Sean Perkins Aug 24 '15 at 20:49
2

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
| improve this answer | |
  • Is there a TSQL command that'll tell me which specific database grew? I have nightly backups on every database on the two servers in question. Also, notifications are sent in the event of failures and succeeds as well as Spotlight alerting. All the database and log file backups have been successful for months. – Sean Perkins Aug 24 '15 at 20:47
  • 1
    I think the easiest in your case would be to go to that PerfMon item (transaction log growths) and in the scope pane at the bottom select each individual DB instead of the 'ALL' option. This will tell you which DB it is. There are also SQL scripts but they require some sort of logging usually where this doesn't – Ali Razeghi Aug 24 '15 at 20:53
  • Awesome, that works for me, thank you for the assistance, the conglomeration of everything, more or less, addresses my matter, thank you! – Sean Perkins Aug 24 '15 at 21:14
  • Anytime, please feel free to reply here if you end up having more questions, it'll pop up on my feed. I'll also add a quick bit of code in the answer to check what is holding the log open once you see it growing. – Ali Razeghi Aug 24 '15 at 21:26
2

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;
| improve this answer | |
  • @ Jens - thank you for your input this helped along with everyone else's response. – Sean Perkins Aug 24 '15 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.