Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log backup question Expand / Collapse
Author
Message
Posted Sunday, December 9, 2012 5:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:48 AM
Points: 26, Visits: 28
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.
Post #1394426
Posted Sunday, December 9, 2012 10:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1394456
Posted Monday, December 10, 2012 1:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 43,014, Visits: 36,173
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



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1394480
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse