Transaction log doesn't finish suddenly
-
We have a transaction log backup that is scheduled to run every 15 minutes typically. Recently it has been running continuously without finishing or failing. If I manually stop the job, it resets the schedule and runs fine every 15 minutes until the next full backup that runs at night. Then after that it runs forever again.
Any ideas? The log file for the maintenance plan does not show any errors.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/ -
Add an output file to the job step which should give you the exact output of the job. Also when the job is running forever, did you find any blockings? Also how big is the transaction log when the job runs forever?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga -
Check if you have any open transactions and how long they have been open for. You may find that there is some dubious code somewhere that is leaving transactions open. Not that I'm suspicious about developers 😀
I love developers, just couldn't eat a whole one
Hope this helps,
Rich[p]
[/p] -
Are you using any third-party backup utility?
We're using the freeware version of Idera SQLSafe on one of our servers. This causes backups for multiple databases to be performed synchronously. When a long backup operation is taking place, subsequent backups seem to be queued.
A few times we've had the exact same issue as you've described after performing a full backup for a 150GB+ database. It seems the Idera SQLSafe service forgets about the queued backups and doesn't return a completion status to the SQL Agent job, which continues to wait for the external task to complete.
Needless to say, it's one of my goals to convince the business that using this freeware product is putting their recovery at risk, and we should be using another product.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply