Each database in SQL Server contains at least one data file and one transaction log file. The transaction log file stores the details of all the modifications that are performed in the SQL Server database. If the transaction log file in a SQL Server database has grown enormously and if you have set the option for the transaction log files to grow automatically, the transaction log file grows in units of virtual log files.
When a transaction log file grows until the log file uses all the available disk space and cannot expand any more, we can no longer perform any data modification operations on your database. Additionally, SQL Server may mark your database as suspect because of the lack of space for the transaction log expansion.
What causes the abnormal log file growth ?
The following are the causes for the abnormal growth of the transaction log,
- Due to Uncommitted transactions: If a user starts an explicit transaction (such as using a BEGIN TRAN statement) and then does a modification of some sort (such as a DDL statement or an insert/update/delete action), the transaction log records that are generated need to be kept around until the user either commits or rolls back the transaction. This means that any subsequent transaction log records generated by other transactions also cannot be freed, as the transaction log cannot be selectively freed. If that user, for example, goes home for the day without completing the transaction, the transaction log will continue to grow as more and more transaction log records are generated but can’t be freed. We can make use of the command, DBCC OPENTRAN (‘Dbname’) to identify if there are any open transactions in the database.
- Due to Index operations: Running DBCC REINDEX, CREATE INDEX, Bulk Insert, Select Into commands, with the database in Full Recovery model will cause the log file to grow enormously, since in full recovery model all the transactions are fully logged.
- Due to Replication: Due to Replication the transaction log size of the publisher database might increase drastically. If there are any transactions running which affect the objects that are marked for Replication and if they remain uncommitted they are not deleted from the transaction log even after a checkpoint or after a log backup. The reason behind this is that the log reader agent which is responsible for moving the transactions from publisher to distributor and which unmarks them afterwards stops functioning for some reason, then the transactions begin to accumulate in the publisher database and will not be freed unless the log reader agent copies those to distributor.
In SQL Server 2005, we can determine the reason as to why the log space is not reused and why the log is not getting truncated using log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view .
If we identify that replication is the cause of log file not getting truncated we can make use of the below procedure sp_repldone.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1
The above procedure can be used in emergency situations to allow truncation of the transaction log when transactions pending replication are present. Using this procedure prevents Microsoft SQL Server 2000 from replicating the database until the database is unpublished and republished.
For more information about the cause for log file growth please refer the below link http://support.microsoft.com/kb/317375/
How to minimize the log file growth ?
The following are the proactive measures in order to minimize the unexpected log file growth,
- If we do not want point in time recovery of your databases then we can change the recovery model to Simple. By doing this, in case of a disaster recovery operation we can recover the database to the most recent full backup only. Since the model database acts as a template for other databases we can set the recovery model as Simple for model database so that in future any new database created will have Simple recovery model.
- 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 in terms of MB instead of %.
- You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.
- Design the truncations to be small.
- Backup the transaction log regularly to remove the inactive transactions in the transaction log if you are using full or bulk logged recovery model. These inactive part of transaction log contains the completed transactions.
Consider the below example,
A B C D
Where point A corresponds to the start of the log file. Let’s assume the point between A and B where some transaction has happened and point B represents the end of the transaction. The space from B to C represents an open transaction still running in SQL at the time of the log backup. When you backup the log, the space from A – B is truncated and freed up for reuse, but because the B – C portion is still active, it cannot be released back for reuse. When you shrink the file, it can only shrink the C-D Portion because the active portion of the log is still B-C.
The following command illustrates how to backup the transaction log in a database,
BACKUP LOG databasename TO DISK='PathFilename.trn'
After the log file is truncated SQL Server will reuse the inactive portion of log, instead of continuing the log file to grow and use more space. If we want operating system to reclaim the truncated inactive portion of log we need to shrink the log file using DBCC SHRINKFILE command as shown below,
USE Yourdbname GO DBCC shrinkfile('logfilename', 1024) GO
For more information about the proactive measures please refer the below link,
What to do if log file has grown enormously and there is no disk space to take log backup ?
When your log file has grown enormously you have 2 options to curtail the growth,
- You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.
Hence please try to avoid the truncate_only/no_log option mentioned above. Due to the above fact the it is deprecated in SQL 2008.
2. In SQL 2008 if the log file is full just change the recovery model to Simple, which will truncate the log file then shrink the log file using the below command.
USE yourdbname GO DBCC shrinkfile('logfilename') GO