Q: Identify which transactions cause growth of SQL Server Transaction Log backups files

Question

Q: Identify which transactions cause growth of SQL Server Transaction Log backups files

Resender

SSCertifiable

Points: 7443
More actions
May 2, 2017 at 9:44 am

#321197

Good day,
So I'm in a bit of a strange situation, here's the scenario.
SQL Server 2008 R2
Has the database for the SAP production server.
There's 6 SQL Server jobs, 2 which run daily, 4 which run once a week.
First daily job that runs takes a backup of the transaction log and deletes any transaction log older then 72 hours, this job runs every 30 minutes.
Second job gathers performance data for the SAP system.
Issue:
Since last week the size of the logfiles is slowly increasing, even during the non-business hours.
There are no mayor jobs running in the SAP system itself.
Question:
How do I identify the transactions inside that log and which one cause the biggest growth of the transaction log.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Are the log backup jobs actually clearing out the transaction logs when they run? If not, use this query to find out why:
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'MyDatabase'

John

Resender SSCertifiable Points: 7443 More actions · Answer 2

John Mitchell-245523 - Tuesday, May 2, 2017 9:56 AM
Are the log backup jobs actually clearing out the transaction logs when they run? If not, use this query to find out why:
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'MyDatabase'
John

Hello John,
So I ran the query and the result is:

Now it is 6PM, it's about 45 minutes after regular business hours are over.
The SAP system itself is not showing any jobs or actions running.
I also ran the following script
SELECT [s_tst].[session_id], [s_es].[login_name] AS [Login Name], DB_NAME (s_tdt.database_id) AS [Database], [s_tdt].[database_transaction_begin_time] AS [Begin Time], [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes], [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd], [s_est].text AS [Last T-SQL Text], [s_eqp].[query_plan] AS [Last Plan] FROM sys.dm_tran_database_transactions [s_tdt] JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id] JOIN sys.[dm_exec_sessions] [s_es] ON [s_es].[session_id] = [s_tst].[session_id] JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id] LEFT OUTER JOIN sys.dm_exec_requests [s_er] ON [s_er].[session_id] = [s_tst].[session_id] CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est] OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp] ORDER BY [Begin Time] ASC; GO
But for the 2 transactions it found

Now to give a hint on the situation

Two weeks ago those log files were averaging 300 mb every 30 minutes.
Last week they went up to 400mb.
And now there flirting with 600mb.

About 30 minutes ago I had at least 2 transactions running for more then 12 hours, but I need to be able to identify them reliably.

There should be a way to identify whats causing this bloating of the files while nothings going on.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Try running a trace to capture queries that run for more than, say, five seconds. That'll give you an idea of what's going on when the server is supposed to be quiet. You may also be able to filter on rows affected or pages written. This from Paul Randal may also help, although support for Extended Events is somewhat limited in SQL Server 2008 - there is no GUI, for instance.

John