To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:
- Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
- Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
- Change the recovery model. By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups. By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database. You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.
- Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
- Design the transactions to be small.
- Make sure that no uncommitted transactions continue to run for an indefinite time.
- Schedule the Update Statistics option to occur daily.
- To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.