shrink file not shrinking the log

[mebenz]

After running maintenance, the log files are growing from 50Mb to over 4Gb each and then makes our database and log backups even bigger.

I have setup a job to shrink the log between log backups, but it is not shrinking the log at all. The only way I've been able to shrink it, is to change recovery to simple, shrink log, then back to full.

use [db] DBCC SHRINKFILE (N'dbLog.ldf', 500)

Is there any reason why it is not shrinking?

 

[mrdenny]

You should leave the transaction log at the 4 Gig size. Shrinking and expanding the log puts un-needed stress on the hard drive when it should be spending it's time processing user requests. Having a large empty data file doesn't increate the size of the backups. What's increasing the size of the backups is the large amount of data in the log when it's being backed up (the data changes from the maintenance plan).

Changing a database recovery mode basically invalidates the prior backups as a new full backup must be taken after the recovery mode change is made. You should stop changing the recovery mode.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mebenz]

During the course of 1 full week, the log file doesn't grow bigger than 100mb. The optimizing and rebuilding causes the log to grow to that length, then all the log backups after that are filling up the hard drive.

 

[mebenz]

Since I backup my database before performing the maintenance, could I add a step to change the database to simple recovery before running the maintenance? If in simple recocery, it will not cause the log to get so huge.

 

[mrdenny]

Yes you could do this, you will then need to take another full backup directly after the database is changed back to full recovery mode.

The much preferrend solution would be to purchase more hard drive space so that you have a continues backup from week to week incase you loose a full backup for some reason.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog