September 29, 2010 at 1:38 pm
database recovery model = full
nightly backup of database and transaction log via a scheduled maintenance plan in sql server management studio
shouldn't that shrink the transaction log?
Thanks...
Marge
September 29, 2010 at 6:10 pm
September 30, 2010 at 5:42 am
The Full recovery model is set up to allow the transaction log to retain data over time. You can then backup the log on a regular basis, say, once every 1/2 hour, for example. This ensures that, in the event of an emergency, you will have all completed transactions available in the log backup up to 1/2 an hour prior to the problem.
So, if you set the database to Full, you must also schedule log backups. If you don't, the log will just continue to grow until it fills the drive(s) allocated to it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 30, 2010 at 8:08 am
Edogg (9/29/2010)
Log backups do not shrink the transaction log.Please read through Gail's article on transaction logs. It should clear up any questions you have.
Edogg said: "Log backups do not shrink the transaction log."
http://qa.sqlservercentral.com/articles/64582/ says:
"Full Recovery model
"...Inactive log records are retained in the transaction log until a lob backup occurs.
"...Because log records are not discarded until they have been backed up..."
So, my question remains. I'm doing a transaction log backup nightly. Shouldn't that shrink the transaction log??
September 30, 2010 at 8:34 am
inevercheckthis2002 (9/30/2010)
Edogg (9/29/2010)
Log backups do not shrink the transaction log.Please read through Gail's article on transaction logs. It should clear up any questions you have.
Edogg said: "Log backups do not shrink the transaction log."
http://qa.sqlservercentral.com/articles/64582/ says:
"Full Recovery model
"...Inactive log records are retained in the transaction log until a lob backup occurs.
"...Because log records are not discarded until they have been backed up..."
So, my question remains. I'm doing a transaction log backup nightly. Shouldn't that shrink the transaction log??
Shrink the log? No. Empty the log, yes.
But full recovery and only a nightly log backup... that's not the way to go. If you're going for full recovery you should be backing up more frequently. Otherwise, you're facing a situation where you may only be able to recover to the last full backup (which runs at the same time as the nightly log backup, right?). This is because, depending on the failure you're dealing with, you can't always take a tail log backup before you start the recovery process.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 1, 2010 at 2:24 am
I agree. If you're only doing one transaction log backup a day you might as well put the database in Simple recovery mode and skip the log backups entirely.
October 1, 2010 at 5:19 am
If you want your log to shrink after the backup you can add a step to shrink it, but if it's going to grow again during the day you'll just end up wasting IOs.
Either switch to simple recovery or do what is described above.
October 1, 2010 at 5:28 am
But, you really don't want to shrink your log over & over. If you had one abnormal set of transactions that caused it to grow, maybe, but if it's because you're not backing it up regularly, the answer isn't to shrink it. The answer is to either change the model or get the backups in place.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 1, 2010 at 7:34 am
Grant Fritchey (10/1/2010)
the answer isn't to shrink it.
I agree that either the model or the backup strategy needs to be addressed.
However, I think I have a short term need to shrink an 11 GB transaction log. Looking for a way to do this. I see a "Shrink Database Task" in the Maintenance Plan toolbox- would that do it?
October 1, 2010 at 7:37 am
If you absolutely have to shrink it you'd be better off using Shrink Files and selecting the specific ones you want to work on--Shrink Database would attempt to shrink the data files as well, and you *really* don't want to be doing that!
October 1, 2010 at 7:43 am
Yeah, use the shrink file function, not shrink database.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply