December 9, 2012 at 5:44 pm
Hello,
My transaction log backup today took longer time to complete than usual. Daily it runs at 1:00PM for about 2 mins and today it took like almost 2 hours. Also for a few databases the size of backup is almost equivalent to size of the full backup.
We had connectivity issues with the SAN(the drive where tempdb sits) yesterday around 5:00PM and had to restart sqlserver services yesterday for 4 times as I was seeing some of the errors as I listed below.
"The log for database 'tempdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database."
The services restart fixed that issue. The errors stopped at 7:00PM after the last restart. But the transaction log backup today grew up to almost the size of full backup (50GB) and took longer time. Usually the size of txlog backup is than 500MB for these databases.
I had a purge job that runs once every saturday night that deletes old data. But cant think of if that wud cause this issue.
Thanks for the inputs.
December 9, 2012 at 10:41 pm
did you check database log size before the backup ?
instead of only once in day,you should configure log backup to execute frequently,say every hour depending of your database size
-----------------------------------------------------------------------------
संकेत कोकणे
December 10, 2012 at 1:35 am
DBA24 (12/9/2012)
My transaction log backup today took longer time to complete than usual. Daily it runs at 1:00PM for about 2 mins and today it took like almost 2 hours. Also for a few databases the size of backup is almost equivalent to size of the full backup.
Index rebuilds ran since the last log backup.
Backing up the log once a day is pretty worthless. If a full day of data loss is permitted you might as well go simple recovery and not worry about the log.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply