Sql-server – causing the Transaction log to grow

sql-server-2008transaction-log

The TLOG in my SQL Server 2008 is growing faster than usual.
The DB has a Full recovery model and we have a TLOG backup every hour.

Can you please write me or refer me to link that describes a way to identify what is the session/process/query that causes the TLOG growth.

I thought that if I'll know WHEN the growth is occurring, I'll be able to detect the job/process that causes the growth.
I've created a job that saves the output of sp_helpdb 'dbname' to a table, but even after knowing the right time, I wasn't able to find the sordid process.

I would appreciate your help in this matter.

Thanks in advance,

Roni.

Best Answer

You can review events from the default trace to identify the growth events you care about:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 93 -- log autogrow event
ORDER BY StartTime DESC;

Combined with a separate trace or extended event session you set up yourself (e.g. to capture all updates with > x duration or > y reads or writes) you can hopefully find some likely culprits around the same time from the same SPID. You won't be able to do that going back in time, of course, but you could set something up to help identify them in the future.

With that all said, your next instinct might be to fight with this process and constantly run up behind it and shrink the log file. This is not a good idea because, really, what's the point? You're going to shrink it to free up space temporarily, only so the same file can grow and fill up the space again? Please read this Q & A in full:

Related Question