We help IT Professionals succeed at work.

Add a step to a system backup job

154 Views
Last Modified: 2017-03-20
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?
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Scott,

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 PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 );
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Scott,

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.

Dale
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks, Scott.  Appreciate all of the assistance.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.