SQL Server Tips n°2 : BEST PRACTICES for Shrinking T-Log Files and database FILES

Best Practices for shrinking transaction log files :

How many times have you received an alert that the disk has run out of space and when investigated you found that the log file is what caused the disk space to run out of space.

In today's tip we will talk about how T-Logs are shrunk and some of the best practices for shrinking T-Log files.

There are fixed boundaries from which a transaction log file can be shrunk. The size of the virtual log files within the log determines the possible reductions in size. Therefore, the log file can never be shrunk to a size less than the virtual log file. Also, the log file is reduced in increments equal to the size of the virtual log file size. For example, a transaction log file of 1 GB can consist of five virtual log files of 200 MB each. Shrinking the transaction log file deletes unused virtual log files, but leaves at least two virtual log files. Because each virtual log file in this example is 200 MB, the transaction log can be reduced only to a minimum of 400 MB and only in increments of 200 MB. To be able to reduce a transaction log file to a smaller size, create a small transaction log and let it grow automatically, instead of creating a large transaction log file all at once.

In SQL Server 2005, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation immediately tries to reduce a transaction log file to the requested size (subject to rounding). You should backup the log file before shrinking the file to reduce the size of the logical log and mark as inactive the virtual logs that do not hold any part of the logical log.

Best Practices for shrinking database files :

Consider the following information when you plan to shrink a database or file:

1) A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

2) Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

3) A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.

4) Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.