Skip to content

## 📝 Author

Birat Aryal — birataryal.github.io
Created Date: 2025-06-18
Updated Date: Tuesday 17th June 2025 21:41:49
Website - birataryal.com.np
Repository - Birat Aryal
LinkedIn - Birat Aryal
DevSecOps Engineer | System Engineer | Cyber Security Analyst | Network Engineer

🗂 SQL Server Transaction Log (LDF) Truncation Guide

This guide demonstrates how to safely truncate and shrink the transaction log file (LDF) of a SQL Server database using T-SQL. It is useful when the log file grows too large due to:

Use Cases

  • Long-running transactions
  • Replication issues
  • Missing backup chain or disabled log backups

Caution

Always back up your database before performing these actions, especially when changing recovery models.

This guide explains how to safely shrink and truncate the data file (MDF) of a SQL Server database using T-SQL. It is especially useful when:

Use Cases

  • The data file has grown significantly, and unused space is not being reclaimed automatically.
  • Large amounts of data were deleted, archived, or moved.
  • Issues like replication lag or long-running transactions have been resolved.

Warning

Shrinking MDF files should be done cautiously and rarely in production. It can cause index fragmentation and impact performance.

Caution

Always back up your database before performing shrink operations and changing recovery models.

📌 1. Identify MDF and LDF File Names

Tip

Run this query to get the logical and physical file names for your target database:

SQL
USE <YourDatabaseName>;
GO

SELECT 
    name AS LogicalFileName,
    physical_name AS PhysicalFilePath,
    type_desc AS FileType
FROM sys.database_files;

Tip

Replace <YourDatabaseName> with the actual name of your database.

🧾 2. Steps to Truncate and Shrink the Files

Follow these steps after identifying the correct file name.

✅ Step 1: Issue Checkpoints

Flush dirty pages to disk before shrinking the database files:

SQL
USE <YourDatabaseName>;
GO

CHECKPOINT;
CHECKPOINT;

🔁 Step 2: Switch Recovery Model to SIMPLE

Temporarily switch to the SIMPLE recovery model to allow truncation:

SQL
ALTER DATABASE <YourDatabaseName>
SET RECOVERY SIMPLE;

📉 Step 3: Shrink the Files

Shrink the transaction log file to a target size (e.g., 1,000 MB = 1 GB):

SQL
DBCC SHRINKFILE (<YourLDFLogicalName>, 1000);  -- Size in MB

Tip

Replace <YourLDFLogicalName> with the actual logical name of the log file.

SQL
DBCC SHRINKFILE (<YourMDFLogicalName>, 1000);  -- Size in MB

Tip

Replace <YourMDFLogicalName> with the actual logical name of the data file.

♻️ Step 4: Revert to FULL Recovery Model

Restore the original recovery model:

SQL
ALTER DATABASE <YourDatabaseName>
SET RECOVERY FULL;

📝 Example (Replace with Your Values)

SQL
USE MyDatabase;
GO
CHECKPOINT;
CHECKPOINT;

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;
DBCC SHRINKFILE (MyDatabase_Log, 1000);
ALTER DATABASE MyDatabase SET RECOVERY FULL;
SQL
USE MyDatabase;
GO
CHECKPOINT;
CHECKPOINT;

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;
DBCC SHRINKFILE (MyDatabase, 1000);
ALTER DATABASE MyDatabase SET RECOVERY FULL;

📌 Notes & Best Practices

🔐 Recommendations

  • Always take a full backup after switching back to FULL to restart the log backup chain.
  • Shrinking should be a one-time operation, not part of regular maintenance.
  • Analyze why the file grew in the first place (e.g., large transactions, replication backlog).
  • Rebuild indexes if fragmentation increases significantly after shrink.

📚 Additional References