Avatar of Dale Fye
Dale Fye
Flag for United States of America asked on

Add a step to a system backup job

Asked a question yesterday about creating a backup job to fully backup my system and all the working databases weekly, and do incremental backups daily.  Looks like this is going to work well.  

So, the next question is:
How do I add a step to the weekly backup to shrink the log files for each of the system and production databases after the full backup is completed?
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Scott Pletcher

Don't (or, as a German friend of mine used to say, "Better don't").  That's just not a good idea.  Continually shrinking and growing the log is huge overhead and causes pauses in processing.

If the log file inevitably reaches a certain size, pre-allocate it to that size in large allocations and leave it alone.
Dale Fye


I've got one log file that is over 23 GB, it gets a massive import of data (several gig) from the clients accounting system once a month, then it is only used for reporting.  I already have the log set to Simple, but would really like to get this file down to a reasonable size.  So, when I do that monthly import, if I do a full backup, does is make sense to shrink the log then?  Since this is just being used for BI reporting, we are not doing daily updates, inserts, or deletions.  I'm now having second thoughts as to whether I need to even backup the transaction log for his database.
Scott Pletcher

Ok, that's reasonable.  You've got one of those exceptional situations.

Yes, it's legit to shrink the log after the import.  And before the import starts, pre-allocate the log to, say, 24GB.

/*after import*/
USE <your_db_name>
DBCC SHRINKFILE(2, 8192) /*or 4096*/ /*after import, shrink log to 8GB (or 4096/4GB), whichever is best for you*/

/*before import*/
USE <your_db_name>
/*EXEC sp_helpfile*/  /*will give you the log's logical file name, which you'll need for the ALTER DATABASE commands*/
/*pre-allocate the log for best processing time, but do it in large chunks*/
/*if 8GB takes too long to pre-format on your disk subsystem, reduce the chunks to 4GB each*/
ALTER DATABASE <your_db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE =  8GB );
ALTER DATABASE <your_db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 16GB );
ALTER DATABASE <your_db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 24GB );
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale Fye


So do I even really need to BU this transaction log, at all or is shrinking it after the full BU sufficient?  

My intent is to modify the script which performs the import to perform a full backup of this table after the import is completed.  I guess I could also modify it to preallocate the log to some large value, before the import, then perform the shrink and deallocate space to the log after the backup is complete.

Scott Pletcher

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

Thanks, Scott.  Appreciate all of the assistance.