When you take the groceries out of the bag (truncate), the size of the bag is still the same, and it's ready for more groceries (same amount) to fit in. Shrinking the bag will make it smaller, and assuming you need to put the same amount of groceries in the following week, you will have less storage space. The groceries won't fit, so don't shrink your grocery bag if you know you need the space!
The obvious solution to prevent log files from growing too large is managing it correctly on the front end (i.e., planning correctly for data and log space, and recovery requirements), but sometimes events occur that require us to be reactive instead of proactive. It happens to the best of us!
Solutions
1. If your database is set to Full Recovery and no Tlog backups are happening
- Set to Simple recovery - do this only if you don't care about point-in-time recovery in the event of disaster. This would most likely used on a test or dev database, and is also suitable for data warehouses with read-only data.
- Leave in Full recovery, but schedule tlog backups on a frequent basis depending upon your tolerance for possible data loss. In a perfect world, production databases should be in Full Recovery mode for disaster recovery reasons. Recovery model and frequency of backups are always going to depend on a business' tolerance for data loss. Backing up your log file once every day or three won't really help with your log size growth, especially in a highly transactional database because the log file will continue to grow until the log is backed up. (I have clients that back up Tlogs every 5 minutes, to every 2 hours...just depends on your business needs).
2. If large inserts or deletes are happening, and it really needs to be that big
- Consider breaking up large transactions into smaller batch jobs
- Consider switching to Bulk Logged Recovery during the large bulk operations. Switching between Full and Bulk Logged will not break the transaction log backup chain.
- Properly size your log file. For large transactions SQL has to be able to see logs back to the beginning of the transaction in case something goes wrong and it needs to recover. The size of your log file should accommodate the largest transaction, or the largest sum of simultaneous transactions that regularly occur.
- Properly size the autogrowth of your log file. When your log needs more room for large transactions, and the auto growth size is set in too small increments, too many Virtual Log Files (VLFs) will be created which takes more time to perform database recovery. In order to see how many VLFs are in your database, check how many records are returned after running this statement:
DBCC LOGINFO
As a common rule of thumb, VLFs should be less than 500, depending on the size of your database. If VLFs greatly exceed this amount, take these steps:
1. Take backup of your log file (may need to do this twice)
2. Shrink the log file
USE MyDatabase;
GO
DBCC SHRINKFILE (MyDatabase_log, 1);
GO
- shrink log file as small as it can go, preferably close to 1MB
- may have to shrink it gradually, in smaller chunks
3. Re-grow the log file to a practical size, which should accommodate the largest transaction, or the largest sum of simultaneous transactions that regularly occur. *Depending on the size you want your log file to be, consider growing your log file in multiple chunks. Kimberly Tripp recommends growing your log file in 8GB chunks...read this for more info: http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
USE MyDatabase
GO
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME = MyDatabase_log, SIZE = 8000MB)
- grow the log in multiple chunks (i.e. 8GB, then 16GB, etc)
4. Set auto grow size to larger chunks of space, so less VLFs are created during auto grows
USE [master];
GO
ALTER DATABASE MyDatabase
MODIFY FILE
(NAME = MyDatabase_log, FILEGROWTH = 1000MB);
GO
3. If Long running transactions are running (index maintenance or bulk inserts)
- Again, properly size your log file. (See steps above). Log files should be created at the desired size when the database is created, rather than allowed to slowly grow over time. However if this was not done initially, then shrinking the log file and manually re-growing it to the desired size is your next option.
- If possible, consider breaking up large transactions into smaller batch jobs
Conclusion
Being proactive in managing your SQL environment is always recommended for preventing problems, but occasionally things happen which require us to deal with issues. Instead of creating your database log file with a small amount of space and allowing it to automatically grow in small amounts, create it with enough space from the start, so that it only grows on rare occasions. Properly managing your database file sizes and auto growth will help ensure your disk space doesn't run out, and also it will help improve the performance of your database.
