How to shrink the transaction log file ?

Is the transaction log file 20GB? Most frequent reason: database in FULL recovery model, but only FULL backups are done (no transaction log backups). In such a case put the database into SIMPLE recovery model for automatic log file management. Example script:

USE [master]

GO

ALTER DATABASE [AdventureWorks2008] SET RECOVERY SIMPLE WITH NO_WAIT

GO

Backup the transaction log or the database (full backup) prior to log file shrinking. Exit all users from DB.

Simplest method: in SSMS Object Explorer change Recovery Model to SIMPLE (DB properties, options). Then shrink log file (DB tasks, shrink, file, File Type : log). Change database back to original FULL Recovery Model only if you plan to do transaction log backup for disaster recovery. For FULL DB backup only disaster recovery, leave database in SIMPLE mode. 

Related warning article: Why you want to be restrictive with shrink of database files 
 
The size of the log files, if there are inactive portions, are physically reduced in the following situations:
1. A DBCC SHRINKDATABASE statement is executed

USE master;

DBCC SHRINKDATABASE (AdventureWorks, 10);

GO

NOTE:

A. A database shrink operation may increase fragmentation of the indexes. Therefore you should not shrink the database or a data file(s) after rebuilding indexes. Repeated shrink and growth of the database may lead to file system level fragmentation as well.

B. A database needs "elbow room" for proper operations. Therefore prior to shrinking the database, other options should be considered such as purging/archiving not needed/rarely used data, or adding more disk space.

Alternate script:

-- First do a FULL BACKUP with verification - sql server shrink database

-- This script will not work in SQL Server 2008 - sql server shrink log file

BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY  

go  

DBCC SHRINKDATABASE (AdventureWorks, 10, TRUNCATEONLY)  

go 

2. A DBCC SHRINKFILE statement referencing a log file is executed

USE AdventureWorks;

DBCC SHRINKFILE (AdventureWorks_Log, 10)

GO

3. An autoshrink operation occurs (database option turned on)

NOTE: It is recommended that you keep the AUTOSHRINK option off, unless you have some requirement to keep it on (see Note for 1.).
Solutions for Transaction Log Size Reduction

Execute DBCC OPENTRAN to check for open transactions. If any, resolve issue prior to shrinking.

-- DBCC OPENTRAN Simulation

use AdventureWorks;

BEGIN TRANSACTION

update HumanResources.Department

set ModifiedDate=dateadd(day,0,ModifiedDate)

go

/***** OPEN A NEW CONNECTION FOR THE FOLLOWING *****/

DBCC OPENTRAN

/* DBCC OPENTRAN results:

Transaction information for database 'AdventureWorks'.

Oldest active transaction:

    SPID (server process ID): 55

    UID (user ID) : -1

    Name          : user_transaction

    LSN           : (1541:376:414)

    Start time    : Dec 19 2008  7:01:47:670AM

    SID           : 0x01050000000000051500000033027d44f1457f89cf9b73b7ed030000

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

*/

-- Use the SPID from above 

DBCC INPUTBUFFER(55)

/* Results

EventType   Parameters  EventInfo

Language Event    0     use AdventureWorks;    BEGIN TRANSACTION 

update HumanResources.Department 

set ModifiedDate=dateadd(day,0,ModifiedDate) 

*/

/***** END OF OPEN A NEW CONNECTION FOR THE FOLLOWING *****/

ROLLBACK TRANSACTION

go

Shrinking a log depends on first truncating the log. In simple recovery mode, truncation is automatic. In full recovery, it occurs upon log backup (below) or full backup. 

BACKUP LOG AdventureWorks TO DISK='f:\data\backup\AWlog.bak'

Log truncation does not reduce the size of a physical log file. However, it does reduce the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size. The log_reuse_wait_desc flag in sys.databases and DBCC LOGINFO command can be helpful to resolve block to shrinking. 

SELECT DBName=name, log_reuse_wait_desc

FROM sys.databases

WHERE log_reuse_wait_desc != 'NOTHING'

ORDER BY database_id

DBCC LOGINFO

/*

FileIdFileSize    StartOffset FSeqNo      Status      Parity      CreateLSN

2     458752      8192        24          0           128         0

2     458752      466944      25          0           128         0

2     458752      925696      26          0           128         0

2     712704      1384448     30          2           128         0

*/

Status = 2 (active VLF) has to be on the top or SQL Server will not shrink the log. Successive application of combination of transaction log backups and adding dummy transactions on dummy table will move "2" to the TOP.

Article: Why you want to be restrictive with shrink of database files

If there is no shrinking, detach/attach db, put db into single user (SINGLE_USER) mode, and attempt SQL shrink log file again. Return database to MULTI_USER mode. Also check for live or disabled/deleted replication interference with shrinking. 

Complete Transaction Log File Rebuild

If none of the above SQL shrink log file methods work, you can create a brand-new empty transaction log file (and delete the current log file which is renamed later when satisfied with db operations) the following way:

Assume the database name is dbOMEGA;

Exit all users from the database

Backup database dbOMEGA

In SQL Server Management Studio Object Explorer:

Right click on dbOMEGA -> Properties -> Files -> make note of the PATHs for .mdf and .ldf

Select dbOMEGA -> Tasks -> Detach... -> Click on OK

In the log file folder for dbOMEGA (from Properties) -> rename the dbOMEGA_log.ldf to dbOMEGA_log_to_be_deleted.ldf

Click on Databases in OE -> Attach... -> Click Add -> add the database dbOMEGA .mdf file (MDF file location), select the log file and click on the REMOVE button, click on OK at the bottom

After verifying and using the database with the new transaction log, the old log file can be deleted (like a week later) 

Source : sqlusa