How to Trouble Shoot Transaction Log file Growth in SQLServer

How to Trouble Shoot T-Log file Growth
============================

Enable the following TF for checkpoint:-

TRACEFLAG 3512
Description ='Write info on log space used at checkpoint time to errorlog'
 
TRACEFLAG 3502
Description ='Send checkpoint state changes to errorlog'
 
TRACEFLAG 3504
Description ='Send checkpoint summary to errorlog ,Displays number of pages written and other stats'
 

Make sure this TF 3505 is not enabled which TF 3505Allows checkpointing to be dynamically disabled'
 

Create a database named Log_space_analysis and create these tables
===================================================

Create table LogSpaceusage
(dbname varchar(20), Log_Size_MB varchar(20), Log_Space_Used varchar(20), Status
varchar(20), rdate datetime default getdate())

Create table OpenTran
(trantag varchar(30), tranvalue varchar(46), rdate datetime default getdate())

CREATE TABLE [dbo].[trandetails]
([transaction_id] [bigint] NOT NULL, [session_id] [int] NOT NULL, [database_id]
[int] NOT NULL ,[database_transaction_begin_time] [datetime] NULL,
[database_transaction_log_bytes_used] [bigint] NOT NULL,
[database_transaction_log_bytes_used_system] [int] NOT NULL,
[database_transaction_log_bytes_reserved] [bigint] NOT NULL,
[database_transaction_log_bytes_reserved_system] [int] NOT NULL,
[database_transaction_log_record_count] [int] NOT NULL, [rdate] [datetime] NOT NULL

) ON [PRIMARY]

Run this three particular queries in different query windows (or) as different jobs just Before we start the operation which consumes Log.

Query 1
------------

while (1=1)
begin
DECLARE @string VARCHAR (255)
SELECT @string = 'DBCC sqlperf(''logspace'') '
INSERT INTO logspaceusage
(dbname,Log_Size_MB ,Log_Space_Used,Status)
EXEC (@string)
waitfor delay '0:00:30'
end

Query 2
------------
while (1=1)
begin
DECLARE @string VARCHAR (255)
SELECT @string = 'DBCC OPENTRAN(''Database_name'') WITH TABLERESULTS'
INSERT INTO OpenTran
(trantag, tranvalue)
EXEC (@string)
waitfor delay '0:00:30'
end

Note: In the select query we have to give the name of the database, of which the
log size was growing , instead of Database_name.

Query 3
------------

while (1=1)
begin
insert into trandetails
select
b.transaction_id,
b.session_id,
a.database_id,
a.database_transaction_begin_time,
a.database_transaction_log_bytes_used,
a.database_transaction_log_bytes_used_system,
a.database_transaction_log_bytes_reserved,
a.database_transaction_log_bytes_reserved_system,
a.database_transaction_log_record_count,
getdate() rdate
from sys.dm_tran_database_transactions a,sys.dm_tran_session_transactions b where
a.transaction_id=b.transaction_id
waitfor delay '0:00:30'
end

 

"select * from trandetails" will give you the transaction and session  which consumes T-Log (we can Sort it based on Log bytes used)

Do not miss to Kill the query 1,2 and 3 once you collected the data.

If possible Create a server side profiler trace with these events ERRORS and Warnings, RPC
Starting, RPC Completed, SP Starting, SP Completed, SP-SQL STMT Starting, SP-SQL
STMT Completed, TSQL-SQL STMT Starting, TSQL-SP SQL STMT Completed. 

Regards

Karthick PK