Shrink SQL Server log file

PROBLEM:
My SqlServer database log files are too big

SOLUTION:
If this is a development or testing environment and you don't need the log backup, you can change the recovery model to simple and then use DBCC SHRINKFILE command.

USE [db_name]
GO
ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(N'db_name_log', 1)

The following code shrink the log for databases in FULL recovery mode.
In production environment, this breaks the chain of the logs and in future you will not be able to restore point in time so you must take log backup before

USE [db_name]
GO
DBCC SHRINKFILE(N'db_name_log', 1)
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(N'db_name_log', 1)
GO

Problems when trying to shrink log

1. Long running transaction - Can prevent transaction log truncation.  These types of transactions can range from transactions being blocked from completing to open transactions waiting for user input. The longer the transaction remains open, the larger the transaction log can grow.  To see the longest running transaction on your SQL Server instance, run the following statement

DBCC OPENTRAN

If there are open transactions, DBCC OPENTRAN will provide a session_id (SPID) of the connection that has the transaction open.  You can pass this session_id to sp_who2 to determine which user has the connection open

sp_who2 SPID

You can determine the SQL statement being executed inside the transactions using the DBCC INPUTBUFFER() statement to return the first part of the SQL statement

DBCC INPUTBUFFER(SPID) --from DBCC OPENTRAN

2.  Backup or restore operation - In SQL Server 2005 and later, you can create a transaction log backup while a full or differential backup is occurring, but the log backup will not truncate the log due to the fact that the entire transaction log needs to remain available to the backup operation.  If a database backup is keeping your log from being truncated you might consider cancelling the backup to relieve the immediate problem.

3. Replication - The inactive portion of the transaction log is not truncated until transactions have been replicated to the distributor.  This may be due to the fact that the distributor is overloaded and having problems accepting these transactions or maybe because the Log Reader agent should be ran more often.  IF DBCC OPENTRAN indicates that your oldest active transaction is a replicated one and it has been open for a significant amount of time, this may be your problem.

4. Mirroring - Similar to transactional replication in that it requires that the transactions remain in the log until the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space will grow. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database and restart mirroring.

5. Disk Space - It is possible that you’re just running out of disk space and it is causing your transaction log to error.  You might be able to free disk space on the disk drive that contains the transaction log file for the database by deleting or moving other files. The freed disk space will allow for the log file to enlarge.  If you cannot free enough disk space on the drive that currently contains the log file then you may need to move the file to a drive with enough space to handle the log.  If your log file is not set to grow automatically, you’ll want to consider changing that or adding additional space to the file.  Another option is to create a new log file for the database on a different disk that has enough space by using the ALTER DATABASE YourDatabaseName ADD LOG FILE syntax.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!