July 9, 2010 at 6:45 am
Hi All,
On one of the server the tlog backup was taking nearly 4 hours for a 100gb log file and one of my colleague had killed the backup process expecting to rollback immediately. But still the rollback process is running for more than 24 hours. Will restarting the services will make the database inaccessible for a few mins for recovery process to complete. I am able to take full backups and the log file is filling up the drive and I am unable to take log backups.Please advise.
July 9, 2010 at 7:00 am
First...tell you colleague not to kill a backup :w00t:
If you check Activity Monitor or query sp_who/sp_who2 what wait-type does it show for the rollback process?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 9, 2010 at 7:16 am
I had checked in activity monitor. Its showing BACKUPTHREAD and another one IO_COMPLETION
July 9, 2010 at 7:18 am
You can restart the service. The DB will go into recovery, for how long is another matter. Could be seconds, could be hours.
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
July 11, 2010 at 10:38 pm
Hi Gail,
LogFile size is growing rapidly. Can we use the backup log with truncate_only option to clear the log except the active log and we need to take a full or diff backup immediately. I think this is the best option to minimize the log growth (backup log with truncate_only and full or diff) until the rollback completes.
Thanks
July 11, 2010 at 11:14 pm
I don't believe you'll be able to do that while there's a log backup running, even if it's in rollback.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply