January 30, 2009 at 10:56 am
Ok, a little setup for my question:
I have a db, 6GB in size, with a log file that's almost double that.
It's my understanding that simply doing a full backup will not:
a) Truncate the log
b) Shrink the log file
I had a more senior dba argue with me that it would, and I'm beginning to question myself. I read a few articles (particularly the Intro to the Trans Log by Rea) and I'm still pretty sure of my original stance.
She was looking for a way to shrink the log file back down to a normal size. She had autogrowth enabled, but had placed a limit on the autogrowth. Somehow, the autogrowth went past the limit placed on it (I had no idea that was possible!) so she was looking for a way to shrink the log.
They aren't doing any log backups from what I understand, just full backups every night. I'm guessing the don't need point in time recovery since they don't do those log backups, so I was going to have her switch to simple recovery. That should keep the trans log manageable right? Or will we experience similar growth?
The only reason I can see for the file being so large is no one ever maintained the log, and this db has been around for a couple of years.
But back to my original question: A full backup won't do anything to the trans log correct?
Will the log ever loop back around and overwrite un-needed transactions automatically?
Or do I need to truncate the log after a full backup to free up the space manually?
January 30, 2009 at 11:02 am
But back to my original question: A full backup won't do anything to the trans log correct?
Yes. Full backup will not shrink transaction log.
Will the log ever loop back around and overwrite un-needed transactions automatically?
No. There would be no over-writing of un-needed transactions. there are only two types of transactions - either committed or non-committed. Either way, there would be no re-written.
Or do I need to truncate the log after a full backup to free up the space manually?
Yes. Backup transaction log after full backup and then shrink TLog file with dbcc shrinkfile statement.
SQL DBA.
January 30, 2009 at 11:05 am
Josh Turley (1/30/2009)
Ok, a little setup for my question:I have a db, 6GB in size, with a log file that's almost double that.
It's my understanding that simply doing a full backup will not:
a) Truncate the log
b) Shrink the log file
I had a more senior dba argue with me that it would, and I'm beginning to question myself. I read a few articles (particularly the Intro to the Trans Log by Rea) and I'm still pretty sure of my original stance.
She was looking for a way to shrink the log file back down to a normal size. She had autogrowth enabled, but had placed a limit on the autogrowth. Somehow, the autogrowth went past the limit placed on it (I had no idea that was possible!) so she was looking for a way to shrink the log.
They aren't doing any log backups from what I understand, just full backups every night. I'm guessing the don't need point in time recovery since they don't do those log backups, so I was going to have her switch to simple recovery. That should keep the trans log manageable right? Or will we experience similar growth?
The only reason I can see for the file being so large is no one ever maintained the log, and this db has been around for a couple of years.
But back to my original question: A full backup won't do anything to the trans log correct?
Will the log ever loop back around and overwrite un-needed transactions automatically?
Or do I need to truncate the log after a full backup to free up the space manually?
Both the Full Backup and Differential Backup backup enough of the transaction log to ensure a consistant database when restored. Other than that, they do nothing to the transaction log. The only way to truncate the transaction log is with transaction log backups. Please realize that this does not mean shrinking the physical size of the transaction log. It simply allows SQL Server to reuse space already allocated in the transaction log file(s).
You need to use DBCC SHRINKFILE to reduce the actual (physical) size of the transaction log, and that may not occur if the active portion of the log is near the end of the file.
If there is no need for point in time recovery, then the database should be using the simple recovery model. This will automatically truncate the transaction log after active transactions are commited (written) to the actual database. Remember, all updates regardless of recovery model are first written to the transaction log.
Does this help?
Gail Shaw has written at least one article (I believe) on SSC regarding backups. You may want to do a search of the articles on this site and read it. Very informative.
January 30, 2009 at 11:08 am
Actually, to save you time here is a link to the article: http://www.sqlservercentral.com/articles/64582/
January 30, 2009 at 11:08 am
I'll just add a step to the scheduled job to truncate the log then. I'll do an initial shrink file, but I don't want to do that regularly, because if it needed that much space before, then it will probably need it again.
--Edit--
Unless they don't need point in time recovery, then I'll have her just switch it to simple recovery.
January 30, 2009 at 11:14 am
If you are talking about adding backup log with truncate_only, please realize that option is being depreciated and may not be available in future versions of SQL Server. Again, if you don't need point in time recovery (i.e. you aren't taking transaction log backups), you may be better off changing the recover model to simple.
January 30, 2009 at 4:30 pm
Also realize that once you break the log chain (truncating the log this way will break it) - trying to backup the log later will fail. Since you are not currently backing up the transaction log, you won't run into this problem. However, if you put that into the job - and someone else adds a job to backup the transaction logs - that new job will fail.
It would be much better to implement regulary scheduled transaction log backups for now. Try every hour and monitor how much disk space is used. That will give you a general guideline on how large the transaction log will need to be. Once you have backups to determine the largest size, then you can shrink the transaction log to a size a little larger than the largest backup file.
Or, as you said - change it to simple.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2009 at 10:18 am
Josh Turley (1/30/2009)
I'll just add a step to the scheduled job to truncate the log then.
Ur, why?
If you need point-in-time recovery, schedule log backups.
If you don't need point-in-time recovery, set the DB to simple recovery.
There is absolutely no reason to regularly truncate the transaction log
Lynn Pettis (1/30/2009)
If you are talking about adding backup log with truncate_only, please realize that option is being depreciated and may not be available in future versions of SQL Server.
It was deprecated in SQL 2005. It's unavailable in SQL 2008.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply