October 4, 2011 at 2:17 am
Hi friends,
Just want to know why we cant take a full backup of the database in the below scenario.
Log space usage is 100 %
recovery model - Full.
log_resuse_wait_desc = LOG_BACKUP
No active transaction is running.
Upto my knowledge, backup is read from database and write to a file. Dont understand the reason why backup fails when log is full. is it trying to write any thing to log while taking backup?
Thanks in advance.
October 4, 2011 at 3:41 am
If the files are set to grow automatically, then “Yes”.
Per MSDN:
If a backup operation overlaps with a file-management operation or shrink operation, a conflict occurs. Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out. (The time-out period is controlled by a session time-out setting.) If the lock is released during the time-out period, the second operation continues. If the lock times out, the second operation fails.
October 4, 2011 at 3:49 am
Because a full backup does need to write to the log and because, while the full backup is running, other processes need to write to the log.
You need to take a log backup here (not a full backup, a log backup). Additionally, I'm guessing you don't have regular log backups. If that's the case you need to schedule regular log backups or switch to simple recovery model.
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