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?
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?
ASKER
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.
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.
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 );
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 );
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, Scott. Appreciate all of the assistance.
If the log file inevitably reaches a certain size, pre-allocate it to that size in large allocations and leave it alone.