Scheduling a Shrink Database in SQL Server 2000
Is there a way to schedule a shrink database after the transaction log has been backed up for a database via the maintenance plan?
Software/Hardware used:
Software/Hardware used:
ASKED:
February 2, 2009 4:41 PM
UPDATED:
February 3, 2009 11:29 PM
Answer Wiki:
Yes, you can simply add another job step to use the DBCC SHRINKFILE statement.
However it is typically recommended that you not shrink the files on a regular basis. Doing so causes the database engine and disk subsystem to do extra work that it doesn't need to do. If the log has grown to that size there is a reason for that, and the log will probably grow to that size again. Shrink the file after each log backup gives you a false sense of security.
To see all answers submitted to the Answer Wiki: View Answer History.
Is your database in simple or fully logged recovery mode?
If fully logged you could considered increasing the frequency of your transaction log backups.
As Mr.D says it’s best not to shrink at all (from a performance perspective) if at all possible.
Regards,
SCM.