–by Ginger Keys
Recently I had a client who, after speaking with their software vendor, became overly fearful of their database log files growing too big and taking up all of the disk space on their server. The software vendor is apparently used to having clients that don’t have a DBA on staff, and made some recommendations that would not have allowed my client to recover data to point in time (i.e. switch to Simple recovery, shrink the log file, switch back to Full recovery). I informed my client that this is a very bad idea since they needed to be able to recover as much data as possible if something happened to the database, and promptly undid these changes. I decided to take a look around their systems, and saw the log file for one of the production databases was very large… but there were valid reasons it needed to be that large.
Hopefully you will never have to deal with an ill-informed vendor like this, but if you should find that your log file has grown very big, there are usually some common reasons for this.
Most common reasons:
It’s important to have enough room in your log file for SQL to do its work. Transactions in your database (i.e. changes to data) are logged and these records are written sequentially to the log file. As soon as the transaction has committed, a checkpoint has written the changes to disk, or a backup of the tlog has occurred, these records are no longer needed and SQL will mark the log files ready for re-use. The most ideal situation is to size your log file correctly from the beginning.
Think of your log file as being like a reusable grocery bag that you use and fill each week.
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!
1. If your database is set to Full Recovery and no Tlog backups are happening
2. If large inserts or deletes are happening, and it really needs to be that big
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
DBCC SHRINKFILE (MyDatabase_log, 1);
— 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/
ALTER DATABASE MyDatabase
(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
ALTER DATABASE MyDatabase
(NAME = MyDatabase_log, FILEGROWTH = 1000MB);
3. If Long running transactions are running (index maintenance or bulk inserts)
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.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!