• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

How to use SQL Transaction log to analyze high usage.

Something was running hard against a database and caused our transaction file to fill and expand repeatedly.

What is the best way to analyze the transaction file to find information about the transactions that were causing the problem?

At this point the transaction file has been backed up.
1 Solution
Vitor MontalvãoMSSQL Senior EngineerCommented:
At this point the transaction file has been backed up.
That means the transaction log has been truncated and any relevant information just gone.
With that said I think you can't investigate anymore what happened.
Next time better think to do is to launch a SQL Profiler to capture the current activity so you can see what's happening in the SQL Server instance.
Or use this:


just run the script you download to create the sp_whoisactive stored procedure and then you just execute it to see what is going on on the server:

EXEC sp_whoisactive

Details here:

Scott PletcherSenior DBACommented:
The default trace can show when a log grew.  Sometimes just knowing the times of log extensions can help you determine what caused the issue.

Within the trace, EventClass = 93 is "log file autogrow", so look for those class events in the default trace.

You can use function:
to read the trace file(s).

Typically that trace data stays around a while, assuming you allowed roll-over files.
Jason clarkDBA FreelancerCommented:
If the above solutions doesn't work for you then you can also try  the Transact-SQL TRY…CATCH construct. For more information with examples that include transactions, see: https://technet.microsoft.com/en-us/library/ms175976(v=sql.110).aspx
Vitor MontalvãoMSSQL Senior EngineerCommented:
MikeMOD, a feedback will be appreciated.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now