Karthick PK 's Blog

Karthick PK's technical blog covering topics such as SQLServer troubleshooting, technologies and security.

How to Trouble Shoot Transaction Log file Growth in SQLServer

How to Trouble Shoot Transaction Log file Growth in SQLServer

Rate This
  • Comments 0

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
Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post