August 21, 2009 at 3:50 am
Hi folks,
I'm posting this to the newbies forum because it's a newbie question (I don't have to admin a SQL Server database yet!).
I've been reading about what to do with a database that has a transaction log that has auto-expand turned on, but has filled up the disk.
Now I know that the answer to this question is NOT "stop SQL Server and delete the log" (!!) but what I am curious about is why you would need to do a full transaction log backup.
Perhaps I'm missing something, but if I'm running the database under the simple recovery model, then why wouldn't I just be able to truncate away the inactive portions of the log?
August 21, 2009 at 3:57 am
Oh man... not 5 minutes after I posted that question did I realise what I was missing.
The article I was reading was transaction log truncation.
Turns out what I'm missing is that under the simple recovery model all you need to do is a checkpoint, then a shrink operation - which I just read about here.
A bit more digging shows that I didn't understand the different recovery modes properly... for simple database admin where point in time restoration isn't required then simple recovery mode is sufficient, but you risk losing more data... whereas for data recovery up to a point in time then you need the full recovery model - which of course requires a more advanced backup strategy - one that involves log backups.
So I'm off to read up on backup strategies under full recovery model.
Next time I go to post a question, I'm going to read the Microsoft article more carefully. Sigh.
August 21, 2009 at 5:07 pm
I'm sure someone will appreciate your update.
Thanks for the info.
August 21, 2009 at 5:41 pm
August 22, 2009 at 2:06 am
ta.bu.shi.da.yu (8/21/2009)
Perhaps I'm missing something, but if I'm running the database under the simple recovery model, then why wouldn't I just be able to truncate away the inactive portions of the log?
No matter what recovery model, the first step in fixing a full transaction log is finding out why it's full. Even on Simple recovery, there are a couple reasons that could happen.
That's why the first thing that needs doing is to check sys.databases (on 2005 and higher) and look at the log_reuse_wait_descr column. If it reads 'REPLICATION', there's no point in running a checkpoint. If it reads 'OPEN TRANSACTION' there's no point in running a log backup, etc. On SQL 2000, check sys.processes and DBCC OPENTRAN.
In Simple recovery, the log is auto-truncated on checkpoint, but that does not mean that a checkpoint will clear the log. It will clear inactive transactions only (transactions where the commit has occurred and the data pages have been written to disk and which have been replicated, if transactional replication occurs)
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
August 22, 2009 at 2:08 am
ta.bu.shi.da.yu (8/21/2009)
Turns out what I'm missing is that under the simple recovery model all you need to do is a checkpoint, then a shrink operation - which I just read about here.
Maybe, but checkpoints run automatically so doing that should not be necessary on simple. If, on simple, the log has filled the drive, there's probably a lot more going on that just checkpoints not occuring
I would not recommend shrinking the log unless you've discovered and resolved the cause of the log growth. Otherwise it's just going to grow back to that size again.
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