March 8, 2006 at 8:11 pm
We've got several databases which get optimized weekly, and as part of that process, the log files grow significantly (to about the size of the mdf file)
I've got a job which performs
1) a checkpoint,
2) shrinkdatabase, and
3) backup with Truncate_only
After running a few times, along with transaction log backups and full database backups, the log files eventually get down to reasonable operating sizes.
Is there some procedure which would more consistently shrink the logs faster? When this procedure runs, I am not concerned about losing the content of the transaction logs as we've got tape backups backing up the backup files, and separate backups (log and full) to a local disk for easy access to older data, etc.
Thanks
Mandeep Binning
March 9, 2006 at 12:50 am
if it does bother you that much, add a step to do a BACKUP WITH TRUNCATE_ONLY and DBBB SHRINKFILE, couple times if needed before the step to do your regular full backup. It works for me all the time but I run it only when I have to. There is a reason for the log to grow. If you take away the space it needs then it has to ask for more space every time it needs causing fragmented.
March 9, 2006 at 2:04 am
Thanks for the tip. I will put these into the script.
BTW, the reason for the concern is that the databases are in the range of about 10 GB total, on SCSI mirrorred drives and the storage is an issue. Also, the growth is due to the optimization, which is done late on Friday nights. There is nothing there that needs to be kept, and performance at that time is of no concern.
A typical day's transaction logs are in the 50 MB range, but the optimization balloons this to, as I said, about the same size as the data files.
March 10, 2006 at 1:25 am
Check out this link: http://codetempest.com/article.php?story=20050703191510469
It's a stored procedure I run as a job on some of my bigger databases right after such optimizations. It shrinks the log down to a targetted amount. You supply the value.
Yes, DBCC Shrinkfile works for the data portion of the DB.
Enjoy.
G. Milner
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply