November 26, 2013 at 3:51 pm
Hi,
I just had a situation where the log file for a DB in Simple mode had blown out to 300Gb and filled the drive. When I checked free space there was 0% free space in the file.
I changed the DB to FULL Recovery and I was able shrink the log down to 10GB without having to take a TL backup first.
Can anyone explain to me what happened in the TL when I changed the mode from Simple to Full to allow me to shrink it down?
Thanks!
November 26, 2013 at 6:46 pm
The next time it is in Simple and it can't be truncated I would suggest that you look at the log_reuse_wait_desc column in sys.databases. Here is a link:
http://msdn.microsoft.com/en-us/library/ms345414.aspx
As you can see from the list you have 9 options, out of those options 6 apply to Simple and they are:
0 - Nothing
1 - Checkpoint
3 - Active Backup or Restore
4 - Active Transaction
7 - Database Snapshot Creation (Usually brief)
8 - Log Scan (Usually brief)
So your options are checkpoints weren't running (I believe that was an issue in 2000), an active transaction was running or an active backup was running. Of these three the only thing that would make sense is that a checkpoint wasn't run until you changed the recovery model. If you encounter it again check the column above and see what it is waiting on.
November 26, 2013 at 10:11 pm
JayK (11/26/2013)
Can anyone explain to me what happened in the TL when I changed the mode from Simple to Full to allow me to shrink it down?
I'm going to go with coincidence, because just switching recovery models won't truncate the log. Probably the checkpoint.
See http://www.sqlservercentral.com/articles/Administration/75461/ and http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
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