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


Comments (0)