question

butterfly avatar image
butterfly asked

Tlog for simple recovery db growing out of control

I have a db that uses simple recovery and the log file is growing out of control I tried to shrink the log file and it worked but after the full back up ran the log file again jumped, it went up 50 gigs over the weekend. Help
shrink-databaseshrink-log-filelog-file-size
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs off of voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer leads to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·

3 Answers

·
JohnM avatar image
JohnM answered
It sounds like you've got a large/long running transaction which can't roll around the log file until it has completed, thus pushing out the size. What type of processing is going on over the weekend? I'd start with some type of monitor (server side trace, extended events, 3rd party, etc) to see if you could capture what might be blowing out the transaction log size. Hope this helps!
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Something was running based off of what you're saying. If you shrank the log file down to a much smaller size and then over the weekend it grew back out, there's a transaction doing something. How about maintenance? Index rebuilds? Some other ETL/Job that might be running? What was the size of the log file before the weekend? You also might want to look at the default trace to see if you can identify the SPID that is causing the log growth.
1 Like 1 ·
butterfly avatar image butterfly commented ·
This is a Change Auditor Archive DB and the agent job that's suppose to running on the reg DB and moving trans over 90 days to the archive db is not running so I don't see how it was doing anything over the weekend.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Yep. The only reason you'd see log growth is because there's something there that needs the log to grow. That's either lots of transactions or big transactions or both.
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
You will need to implement some sort of monitoring solution to find what is causing the log to increase. Here is an [article][1] with a script on SSC that discusses this. Implement a solution like that to determine what is causing the log growth. Otherwise it is just a guessing game as to the cause (other than something is running that is causing it to grow). [1]: http://www.sqlservercentral.com/articles/Log+growth/69476/
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Its probably a long running transaction as the others have suggested but it might be worth just a few minutes to check it isnt connected to this http://williamdurkin.com/2014/06/simple-recovery-log_backup/. You can easily check with the code here https://www.simple-talk.com/blogs/2014/06/10/checking-if-your-simple-databases-need-a-log-backup/.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.