Inexplicable LOG growth on a Simple Recovery model DB

Question

Inexplicable LOG growth on a Simple Recovery model DB

johnnycash

Ten Centuries

Points: 1172
More actions
November 2, 2018 at 10:41 am

#367628

Hi gurus,
For the past two days, one production DB's log grows to the point that fills up the disk where all logs are stored. I have this message in the SQLlog:
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions
This DB is in simple recovery mode. Is there something I could do to find out why is the log growing so uncontrollably? Yesterday when it first happened, I reverted any changes made to SPs that use transaction statements but today, I have the exact issue.
As always, any help is greatly appreciated.
Regards,

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Any large data imports, data updates or deletes occur? The transaction log is still used when a database uses the SIMPLE recovery model.

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 2

Just because a database uses the simple recovery model doesn't mean that it doesn't need transaction log space - just like any other database, every transaction is written to the log, but the recovery model defines what happens then. Full recovery, the transaction is retained in the log until the log is backed up; simple recovery, the transaction is retained until a checkpoint is issued.

Thomas Rushton
blog: https://thelonedba.wordpress.com

johnnycash Ten Centuries Points: 1172 More actions · Answer 3

Thanks gurus!

Doesn't the incremental backup sets the checkpoint? this DB has full backups every friday night and daily incrementals. While this is a production DB, there aren't many apps/services transacting on it and that's why we have this schedule.

Should I move it to Full recovery and do log back ups daily?

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

No, do transaction logs hourly if, for example, you can only afford to lose an hour of data in the event of a disaster.

Checkpoints occur automatically every minute or so.

I recommend you search "managing transaction logs" and do some reading.

John

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

Suggest you start with this: https://www.red-gate.com/library/sql-server-transaction-log-management

Thomas Rushton
blog: https://thelonedba.wordpress.com

C DBA Lockhart SSC Veteran Points: 205 More actions · Answer 6

The one thing that I've found most often causes notable log file growth in my databases is Index maintenance, which may also be related to your lock issue.

johnnycash Ten Centuries Points: 1172 More actions · Answer 7

Gurus,

Thank you for your infinite wisdom, and Thomas in particular for the recommended link, that was a very interested read and time very well spent. I found the culprit using what I've learned from that ebook and my log's growth is back to normal.

As it turns out, there is a Windows service that runs a series of SPs and one of those SPs was calling a procedure that did not have the Commit statement. It had the Rollback but no commit anywhere. Further investigation revealed that one of the developers was testing a new implementation in the lab server and accidentally that implementation was brought over to the production box.

If it wasn't for Thomas' recommendation, I am not sure I would've found it. So, Thomas, I owe you a Beer 😀

Once again, thank you everyone!