How to Shrink Your Database/Log File

There are many ways to shrink log file, the easiest by using the Management Studio(2005/2008) or the Enterprise Manager (2000). Right click on the database, mouse over to the tasks then shrink. Select database to shrink all related files to the database or select Files to shrink only selected file. Select reorganize files before releasing unused space and then click OK.

However, sometime MS SQL Server does not allow database shrink due to not enough space left. When that happen, there is a simple steps as follows (this is not highly recommended):

1.  Detach the database (you have to make sure no connection to the database before do this, and make sure you have valid backup).
2.  Locate physically the log file and rename the log file (we can delete it later).
3. Attach the database without the log file; system will create a new log file for the database.
4. After successfully attached, we can delete the log file.

Alternatively we can shrink log file using DBCC script. We have the option to shrink the database by using the DBCC SHRINKDATABASE (which targets all files for the database .mdf and .ldf) or by using the DBCC SHRINKFILE (which targets a particular database file).

Imagine we have database name Northwind to shrink this database simply execute the following commands:

DBCC SHRINKDATABASE (Northwind)

Results

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

10     1           1017768     128         1017744     1017744

10     2           63          63          56          56

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHRINKFILE(Northwind_Log)

Results

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

10     2           63          63          56          56

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sometime we will be surprise by the following results:

Results

Cannot shrink log file 2 (Northwind_Log) because all logical log files are in use.

The reason logical log file is in used is because it was mark for reuse. To identify the transaction is used we can use the following commands:

DBCC OPENTRAN

Results

The result from DBCC OPENTRAN below:

Transaction information for database 'Northwind'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (21543:3418:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What this means is, one of the transaction is mark for reuse (for replication). We will not be able to shrink the log files until the replication status is completed. The easiest method is we can check our replication status and fix the replication accordingly. However, sometimes, there was no replication being setup for the database. This is due to the database was restored from a database being configured as distributor. Since replication is not configured on the current database and the distribution database does not exist, use the following commands:

Execute SP_ReplicationDbOption Northwind,Publish,true,1

GO

Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

GO

To check if the open transaction has been solved, use the following commands:



DBCC LOGINFO('Northwind')


Results 

FileId      FileSize        StartOffset      FSeqNo      Status      Parity CreateLSN

----------- --------------- ---------------- ----------- ----------- ------ ---------

2           253952          8192             13931756    0           64     0

2           253952          262144           13931757    2           128    37576161

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Pay attention to column Status, 0 means Virtual Log Files (VLF) is not in use, 2 means it mark for reuse, thus may also implicated as in used.  In case all status is 0 then we can use above DBCC SHRINKDATABASE and DBCC SHRINKFILE commands. Alternatively we can also use the following commands:

USE Northwind

GO

DBCC SHRINKFILE(Northwind_Log, 1)

BACKUP LOG Northwind WITH TRUNCATE_ONLY

DBCC SHRINKFILE(Northwind_Log, 1)

GO

However we would recommend the following commands for simple recovery model:

USE Northwind
CHECKPOINT
DBCC SHRINKFILE(Northwind_Log, 2)
DBCC SQLPERF(LOGSPACE) 
DBCC LOGINFO 

For full or bulk recovery model use following commands:

USE Northwind
BACKUP LOG Northwind TO DISK = '
DBCC SHRINKFILE(Northwind, 2)
DBCC SQLPERF(LOGSPACE) 
DBCC LOGINFO