Home
%3CLINGO-SUB%20id%3D%22lingo-sub-333999%22%20slang%3D%22en-US%22%3EFinding%20Large%20Transactions%20that%20Bloat%20Your%20Transaction%20Log%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-333999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Apr%2015%2C%202013%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20know%20this%20is%20a%20fairly%20common%20problem%20and%20there%20might%20be%20multiple%20solutions%20out%20there%2C%20but%20I%20figured%20adding%20another%20one%20may%20not%20hurt.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAn%20application%20I%20was%20supporting%20recently%2C%20was%20leading%20to%20100s%20of%20GB%20of%20transaction%20log%20growth%20in%20spurts%20and%20we%20needed%20to%20discover%20what%20was%20causing%20the%20growth.%20I%20built%20the%20following%20process%20to%20help%20with%20the%20discovery%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Create%20a%20script%20titled%20FindLargeTransactions.sql%20and%20paste%20the%20following%20contents%20into%20it%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3Eset%20nocount%20on%20%3CBR%20%2F%3Ego%20%3CBR%20%2F%3Edeclare%20%40datetime%20datetime%20%3CBR%20%2F%3Eselect%20%40datetime%20%3D%20GETDATE()%20%3CBR%20%2F%3Eselect%20%40datetime%20logtime%2C%20text%2C%20tr.database_id%2C%20tr.transaction_id%2C%20database_transaction_log_bytes_used%2C%20database_transaction_log_bytes_reserved%2C%20%3CBR%20%2F%3Edatabase_transaction_log_record_count%2C%20database_transaction_state%2C%20database_transaction_status%2C%20%3CBR%20%2F%3Edatabase_transaction_log_bytes_used_system%2C%20database_transaction_log_bytes_reserved_system%20%3CBR%20%2F%3Efrom%20sys.dm_tran_database_transactions%26nbsp%3B%20tr%20%3CBR%20%2F%3Einner%20join%20sys.dm_exec_requests%20r%20%3CBR%20%2F%3Eon%20tr.transaction_id%20%3D%20r.transaction_id%20%3CBR%20%2F%3Ecross%20apply%20sys.dm_exec_sql_text(sql_handle)%20%3CBR%20%2F%3Ewhere%20database_transaction_log_bytes_used%20%26gt%3B%26nbsp%3B%20100*1024*1024%26nbsp%3B%20--%20100%20MB%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.%20Schedule%20a%20job%20that%20runs%20this%20SQLCMD.EXE%20every%201%20minute%20for%20example.%20Make%20sure%20you%20choose%20an%20output%20file%20for%20the%20job%20step%3B%20this%20is%20where%20the%20contents%20of%20this%20script%20will%20be%20saved.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20sqlcmd%20-SmyServer%5Csql2008r2%20-E%20-ic%3A%5Ctemp%5CFindLargeTransactions.sql%20-W%20-w65535%20-%20s%22%7C%22%20-h-1%20-HFindLargeTransactions%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3.%20Then%2C%20create%20a%20table%20and%20into%20which%20you%20can%20BULK%20INSERT%20the%20data%20for%20further%20analysis%20.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3Edrop%20table%20%5Bdbo%5D.%5BLargeTransactions%5D%20%3CBR%20%2F%3Ego%20%3CBR%20%2F%3ECREATE%20TABLE%20%5Bdbo%5D.%5BLargeTransactions%5D(%20%3CBR%20%2F%3E%5Blogtime%5D%20datetime%20not%20null%2C%20%3CBR%20%2F%3E%5Btext%5D%20%5Bnvarchar%5D(max)%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_id%5D%20%5Bint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Btransaction_id%5D%20%5Bbigint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_transaction_log_bytes_used%5D%20%5Bbigint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_transaction_log_bytes_reserved%5D%20%5Bbigint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_transaction_log_record_count%5D%20%5Bbigint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_transaction_state%5D%20%5Bint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_transaction_status%5D%20%5Bint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_transaction_log_bytes_used_system%5D%20%5Bint%5D%20NOT%20NULL%2C%20%3CBR%20%2F%3E%5Bdatabase_transaction_log_bytes_reserved_system%5D%20%5Bint%5D%20NOT%20NULL%20%3CBR%20%2F%3E)%20ON%20%5BPRIMARY%5D%20TEXTIMAGE_ON%20%5BPRIMARY%5D%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.%20Identify%20the%20job%20output%20file%20and%20replace%20in%20the%20following%20script%20as%20the%20BULK%20INSERT%20source.%20Execute%20the%20BULK%20INSERT%20command%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EBULK%20INSERT%20dbo.LargeTransactions%20FROM%20'c%3A%5Ctemp%5CFindLargeTransactions_10_29_2012.out'%20%3CBR%20%2F%3EWITH%20%3CBR%20%2F%3E(%20%3CBR%20%2F%3EFIELDTERMINATOR%20%3D'%7C'%20%3CBR%20%2F%3E)%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E5.%20Query%20the%20table%20for%20%E2%80%9Clarge%E2%80%9D%20transactions%2C%20i.e.%20ones%20that%20impact%20the%20transaction%20log%20severely.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3Eselect%20database_transaction_log_bytes_used%2F1024%2F1024%20LogMBUsed%2C%20Text%20%3CBR%20%2F%3Efrom%20LargeTransactions%20%3CBR%20%2F%3Eorder%20by%201%20desc%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENamaste!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJoseph%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-333999%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Apr%2015%2C%202013%20%26nbsp%3BI%20know%20this%20is%20a%20fairly%20common%20problem%20and%20there%20might%20be%20multiple%20solutions%20out%20there%2C%20but%20I%20figured%20adding%20another%20one%20may%20not%20hurt.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-333999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EStorage%20Engine%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etroubleshooting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

First published on MSDN on Apr 15, 2013

 

 

I know this is a fairly common problem and there might be multiple solutions out there, but I figured adding another one may not hurt.

 

An application I was supporting recently, was leading to 100s of GB of transaction log growth in spurts and we needed to discover what was causing the growth. I built the following process to help with the discovery:

 

1. Create a script titled FindLargeTransactions.sql and paste the following contents into it:

 


set nocount on
go
declare @datetime datetime
select @datetime = GETDATE()
select @datetime logtime, text, tr.database_id, tr.transaction_id, database_transaction_log_bytes_used, database_transaction_log_bytes_reserved,
database_transaction_log_record_count, database_transaction_state, database_transaction_status,
database_transaction_log_bytes_used_system, database_transaction_log_bytes_reserved_system
from sys.dm_tran_database_transactions  tr
inner join sys.dm_exec_requests r
on tr.transaction_id = r.transaction_id
cross apply sys.dm_exec_sql_text(sql_handle)
where database_transaction_log_bytes_used >  100*1024*1024  -- 100 MB

 

2. Schedule a job that runs this SQLCMD.EXE every 1 minute for example. Make sure you choose an output file for the job step; this is where the contents of this script will be saved.

 


sqlcmd -SmyServer\sql2008r2 -E -ic:\temp\FindLargeTransactions.sql -W -w65535 - s"|" -h-1 -HFindLargeTransactions

 

3. Then, create a table and into which you can BULK INSERT the data for further analysis .

 


drop table [dbo].[LargeTransactions]
go
CREATE TABLE [dbo].[LargeTransactions](
[logtime] datetime not null,
[text] [nvarchar](max) NULL,
[database_id] [int] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[database_transaction_log_bytes_used] [bigint] NOT NULL,
[database_transaction_log_bytes_reserved] [bigint] NOT NULL,
[database_transaction_log_record_count] [bigint] NOT NULL,
[database_transaction_state] [int] NOT NULL,
[database_transaction_status] [int] NOT NULL,
[database_transaction_log_bytes_used_system] [int] NOT NULL,
[database_transaction_log_bytes_reserved_system] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

4. Identify the job output file and replace in the following script as the BULK INSERT source. Execute the BULK INSERT command

 


BULK INSERT dbo.LargeTransactions FROM 'c:\temp\FindLargeTransactions_10_29_2012.out'
WITH
(
FIELDTERMINATOR ='|'
)

 

5. Query the table for “large” transactions, i.e. ones that impact the transaction log severely.

 


select database_transaction_log_bytes_used/1024/1024 LogMBUsed, Text
from LargeTransactions
order by 1 desc

 

 

 

Namaste!

 

Joseph