snippetsqlMinor
How to find what frequent and/or large transaction have taken place in MS SQL Server?
Viewed 0 times
howwhatserversqlplacelargetakentransactionfindand
Problem
Each day is experiencing about 1GB sum total of all the transaction log backups that day, while at the end of that week the Full DB backup is about 180MB. For a business database for buying/selling things from/to vendors/customers the total size of a days worth of transaction log backups is at least 10 times larger than I would expect it should be in comparison to the size of the actual database.
How do I find what sql insert/update/delete/etc operations are causing so many / such large transactions? I'm envisioning that there is some bad code somewhere that re-writes an entire table just to update/insert 1 row, or something that is updating the same rows over and over again. I'd like to know what the bad transaction(s) are before going through all the db application code.
Most questions, including Why Does the Transaction Log Keep Growing or Run Out of Space? and other articles deal with people who have a transaction log file that keeps growing, usually due to them never backing it up. That is NOT the case with my problem. My transaction log file is no longer increasing in size, my question is about how to find what transactions are causing the regular large size of each of the backups, not the size of the log file.
We have an MS SQL Server 2012 database where we are
I realise the actual sizes listed below are not large in the scheme of things due to this DB being only a few months old, but if this trend continues as the DB grows, I'm expecting trouble on multiple fronts. Here is the last 2 week's worth of backup file sizes.
14/11/2014 11:05 PM 20
How do I find what sql insert/update/delete/etc operations are causing so many / such large transactions? I'm envisioning that there is some bad code somewhere that re-writes an entire table just to update/insert 1 row, or something that is updating the same rows over and over again. I'd like to know what the bad transaction(s) are before going through all the db application code.
Most questions, including Why Does the Transaction Log Keep Growing or Run Out of Space? and other articles deal with people who have a transaction log file that keeps growing, usually due to them never backing it up. That is NOT the case with my problem. My transaction log file is no longer increasing in size, my question is about how to find what transactions are causing the regular large size of each of the backups, not the size of the log file.
We have an MS SQL Server 2012 database where we are
- Using the Full recovery model
- Weekly full backups, on Friday nights. (.bak)
- Nightly differential backups (.dif)
- 3 times per day transaction log backups (.trn) (Edit: it has been pointed out this should be much more often for better RPO)
- all backups use compression.
- DB is only used during regular office hours
- DB has an application to perform a variety of tasks including some scheduled ones
I realise the actual sizes listed below are not large in the scheme of things due to this DB being only a few months old, but if this trend continues as the DB grows, I'm expecting trouble on multiple fronts. Here is the last 2 week's worth of backup file sizes.
14/11/2014 11:05 PM 20
Solution
You should be able to dump the output from fn_dump_dblog into a table to sort if needed. I'd recommend you decrease your backup interval to maybe every hour or so to make it easier to analyze the data (plus it will improve your ability to recover and reduce the time each backup takes). I tested a 453mb log and it took 10 minutes to read all 6.7 million rows. If you have a small one to test, take a look so you get an idea of which columns you want to look at like Paul Randal does in this post. I found this blog entry that had some good info on the different operations and more on how to analyze the log.
If you want to watch live transactions or get some nice filtering, I'd use an extended events session since you are on 2012. You can use the wizard if you aren't familiar with it and try the query detail sampling template and add corresponding starting events which should give you a good idea of what statements/transactions are executing. A combination of the two methods might help you correlate how much log a transaction makes since the transaction id is exposed to extended events and it is a column returned by the function.
If you want to watch live transactions or get some nice filtering, I'd use an extended events session since you are on 2012. You can use the wizard if you aren't familiar with it and try the query detail sampling template and add corresponding starting events which should give you a good idea of what statements/transactions are executing. A combination of the two methods might help you correlate how much log a transaction makes since the transaction id is exposed to extended events and it is a column returned by the function.
Context
StackExchange Database Administrators Q#83844, answer score: 2
Revisions (0)
No revisions yet.