Some days back, I had a discussion with one of my colleague about Shrinking a database log file. He told me that they had a database with a log size 10 times larger than the data file size, and they shrink it almost everyday!!! The database was 11GB in size, with 1 GB data file and 10 GB of log file size. The database was in Full Recovery model, and there was no log backup scheduled for the database. Every night, there was a schedule for full database backup.
I asked him to check, whether they can consider to change the recovery model of the database. Since they were not doing a log backup and the database was in Full recovery model, the transaction log file had grown because it contains all the inactive transactions. The only way to reuse the same space, is by taking a transaction log backup. However, if we change the recovery model to Simple, SQL Server will automatically truncate the inactive portion of the log file, which will help in reducing the transaction log file size. Point to remember is that, if the database is in Simple Recovery Model and there is an active transaction going on, then the transaction log file will grow large. To simplify the transaction log management, do one of the following:
- If the database is in Full Recovery Model, schedule the transaction log backup along with the full backup. Make sure, you perform the transaction log backup multiple times to avoid the transaction log growth.
- If you don’t want to take a transaction log backup, then make sure the database is in Simple Recovery Model. SQL Server will automatically manage the transaction log file.
I am sure, most of us, have seen this incident in the past. Hope this post may help someone.. someday..