February 3, 2010 at 9:18 am
Greetings all,
I have an archive database in simple recovery. Data is written once a week to this database. I then perform a full backup of this database.
Immediately after archiving, the log of the archive database grows. Should the full backup truncate the log or should I have to do that explicitly with BACKUP LOG WITH NO_LOG?
February 3, 2010 at 9:25 am
Full backup will not do that.
Even though it is an archive database and is in simple recovery mode - I would not shrink the log file. Grow it to an appropriate size on the disk and leave it. This will decrease the need for it to autogrow every time you archive. File growth is an expensive operation and should be avoided where possible.
The log file used space will return to pre-archival usage levels after you are done with the archive process.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 9:54 am
Thanks for your response. I'll add a bit of info. The archive/backup process is during a 2 day window of inactivity in the system so the performance hit of growing the file is not a big issue for me. The archive log is on the same drive as my source/production database. The production database log size is stable, this db is in full recovery.
My larger concern is providing enough log space for the production database. Given these circumstances I'm wondering if this is a situation where the general best practice you described is not the best approach, that is, would truncating the log of the archive db mean an increase in uptime of my production db because the production log would have more room for growth. Sounds like a question only I can answer.
If I do want to truncate the log after the backup, is BACKUP LOG WITH NO_LOG the correct way to do this?
February 3, 2010 at 9:57 am
I agree with Jason. No reason to shrink if you're going to need the space the next week.
February 3, 2010 at 10:23 am
emily-1119612 (2/3/2010)
If I do want to truncate the log after the backup, is BACKUP LOG WITH NO_LOG the correct way to do this?
No. The DB is in simple recovery. The log will auto-truncate when a checkpoint runs (regularly)
Truncate is not the same as shrink. I would agree with the others, don't shrink the log if you know it's just going to grow again. You're saving nothing.
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
February 3, 2010 at 10:23 am
emily-1119612 (2/3/2010)
Thanks for your response. I'll add a bit of info. The archive/backup process is during a 2 day window of inactivity in the system so the performance hit of growing the file is not a big issue for me. The archive log is on the same drive as my source/production database. The production database log size is stable, this db is in full recovery.My larger concern is providing enough log space for the production database. Given these circumstances I'm wondering if this is a situation where the general best practice you described is not the best approach, that is, would truncating the log of the archive db mean an increase in uptime of my production db because the production log would have more room for growth. Sounds like a question only I can answer.
If I do want to truncate the log after the backup, is BACKUP LOG WITH NO_LOG the correct way to do this?
Truncate would empty the log - which it should pretty nearly be empty already. You would have to use something like dbcc shrinkfile.
As per the production log and growth of that file - since it is in full recovery mode you should have transaction log backups happening on that one. The log also should be right sized so as to prevent growth. Growths may happen but would be indicative of something being wrong. Thus there would be a need to troubleshoot. Otherwise, with good planning and monitoring, neither log would grow or need a lot of space for growth.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 2:10 pm
GilaMonster (2/3/2010)
emily-1119612 (2/3/2010)
If I do want to truncate the log after the backup, is BACKUP LOG WITH NO_LOG the correct way to do this?
No. The DB is in simple recovery. The log will auto-truncate when a checkpoint runs (regularly)
quote]
Ok I have seen different behaviors with the log size which may be explained by the timing of checkpoint.
I am convinced. I'll just run the backup and leave the log as is. Thanks all.
February 3, 2010 at 2:59 pm
emily-1119612 (2/3/2010)
GilaMonster (2/3/2010)
emily-1119612 (2/3/2010)
If I do want to truncate the log after the backup, is BACKUP LOG WITH NO_LOG the correct way to do this?No. The DB is in simple recovery. The log will auto-truncate when a checkpoint runs (regularly)
quote]
Ok I have seen different behaviors with the log size which may be explained by the timing of checkpoint.
I am convinced. I'll just run the backup and leave the log as is. Thanks all.
Prod DB is in simple? I highly recommend that be changed to full recovery and then transaction log backups performed on a regular interval.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 3:47 pm
CirquedeSQLeil (2/3/2010)
emily-1119612 (2/3/2010)
GilaMonster (2/3/2010)
emily-1119612 (2/3/2010)
If I do want to truncate the log after the backup, is BACKUP LOG WITH NO_LOG the correct way to do this?No. The DB is in simple recovery. The log will auto-truncate when a checkpoint runs (regularly)
quote]
Ok I have seen different behaviors with the log size which may be explained by the timing of checkpoint.
I am convinced. I'll just run the backup and leave the log as is. Thanks all.
Prod DB is in simple? I highly recommend that be changed to full recovery and then transaction log backups performed on a regular interval.
Its an archive database that gets updated once a week so simple is fine as I take the backup after the archive process.
February 3, 2010 at 3:50 pm
emily-1119612 (2/3/2010)
CirquedeSQLeil (2/3/2010)
emily-1119612 (2/3/2010)
GilaMonster (2/3/2010)
emily-1119612 (2/3/2010)
If I do want to truncate the log after the backup, is BACKUP LOG WITH NO_LOG the correct way to do this?No. The DB is in simple recovery. The log will auto-truncate when a checkpoint runs (regularly)
quote]
Ok I have seen different behaviors with the log size which may be explained by the timing of checkpoint.
I am convinced. I'll just run the backup and leave the log as is. Thanks all.
Prod DB is in simple? I highly recommend that be changed to full recovery and then transaction log backups performed on a regular interval.
Its an archive database that gets updated once a week so simple is fine as I take the backup after the archive process.
K - that is what I understood. Prod is full recovery model and the archive db is simple.
Good luck
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply