June 23, 2009 at 8:20 am
Due to drive space limitations, we need to shrink the transaction logs for all of our databases once a week. I do this just before I get a full database backup. After doing some research on the web, I found the following steps to be the most commen way to accomplish this:
Set Recovery Model to 'Simple'
Run a CHECKPOINT
DBCC SHRINKFILE
Set Recovery Model back to 'Full'
Here's my question... if a 'BACKUP LOG' command truncates the log, why would I want to set the Recovery Model to 'Simple'? Couldn't I accomplish the same thing with these steps?
Run a CHECKPOINT
BACKUP LOG
DBCC SHRINKFILE
June 23, 2009 at 8:30 am
After you set back to Full Recovery, you need to take a full backup because you have broken the log chain.
June 23, 2009 at 8:32 am
Yes - I indicated in my original post that I shrink the log just before I get a full backup.
June 23, 2009 at 8:36 am
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
June 23, 2009 at 8:42 am
Thanks, Gail. I read through it, and I understand what the log is and how it's used. But that failed to answer my question. Basically, of the 2 blocks of psuedo-code listed, which is a better approach?
June 23, 2009 at 8:48 am
If your database is in full mode, u do not need to change it back to simple. You could just do this
Run a CHECKPOINT
Back up the database
BACKUP LOG with truncate_only
DBCC SHRINKFILE
June 23, 2009 at 8:56 am
HDMan (6/23/2009)
Thanks, Gail. I read through it, and I understand what the log is and how it's used. But that failed to answer my question. Basically, of the 2 blocks of psuedo-code listed, which is a better approach?
The question is, do you need point-in-time recovery? For example, say your full backup is at 8pm and the DB fails at 10am the next day. Is restoring to 8pm acceptable or do you need to restore up to the point of failure.
If restoring to 8pm is acceptable, then switch the DB to simple recovery and leave it alone. No log backups, no worries.
If restoring to 8pm is not acceptable, then you should have regular log backups. In that case you're managing the log yourself and you should schedule log backups often enough to keep the log small. In this case you do not ever want to switch to simple or truncate the log and the log should not grow appreciably.
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
June 23, 2009 at 8:58 am
sarvesh singh (6/23/2009)
BACKUP LOG with truncate_only
Deprecated in SQL 2005, gone in SQL 2008.
Oh, and the full backup goes after the log truncation. If you do it before then the log chain's broken, log backups will fail and there'sd no way to do a point-in-time restore until another full backup is taken.
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
June 23, 2009 at 2:15 pm
Gail - we do need point in time recovery. I do full backups every Friday night at midnight, differentials every other night at midnight, and log dumps every hour. The problem is that I have to shrink the log files when I do the full backups because they do grow to the point where I run out of drive space on the drive that contains them. Until we add more drive space (which hopefully will be relatively soon!), I have no option but to keep shrinking the log files every week. My problem is that it I'm not sure of the best method to do so. Is it to set the recovery model to Simple, as I am now, or just use the BACKUP LOG command?
June 23, 2009 at 2:22 pm
If you set the DB to simple you break the log chain. That may not sound important with a full backup straight after, but what if that full backup's not restorable one time and you have to use log backups. You won't be able to restore log backups past the point where you switched to simple.
If you're taking regular log backups, the log shouldn't be growing much, if at all. What's causing the growth? Find that out and maybe you won't have to shrink.
If you do insist on shrinking, you should be able to do it without a problem after a log backup has run.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply