The .ldf file has grown to hundreds of GB: how to safely reduce the log and stabilize the database

Growth of the transaction file (.ldf) to tens or hundreds of gigabytes means that SQL Server is not truncating the transaction log. This creates a direct risk of:

• the disk filling up completely,

• the program stopping,

• the database switching to Recovery Pending or Suspect states.

In technical support practice, cases have been recorded where .ldf grew to 190+ GB.

When the issue occurs

The situation is typical for databases that:

• run for a long time without scheduled maintenance,

• have active logs of actions and document changes,

• use SQL Server Express without regular monitoring of disk space.

Cause

The .ldf file stores transaction records until SQL Server can free (truncate) the log. If:

• the log is not being truncated,

• there are no regular service procedures,

• there is not enough free disk space,
  the log can grow without limits, even if the database itself (.mdf) hardly changes.

A separate case is the Full recovery model: in this mode, the log is not freed automatically, and truncation requires transaction log backups. The Full model allows point-in-time recovery, while Simple allows recovery only from a full backup. Switching to Simple leads to automatic log truncation and reduces the size of .ldf (it retains only the records required for current operations).

Solution (safe algorithm)

Step 1. Internal data cleanup

Before physically shrinking the log, you need to reduce the amount of information that SQL Server considers active.

Recommended actions:

• clear the “User action log” for older periods,

• clear the “Document change log”, keeping only current data (for example, 2–3 months),

• run the service operation
  “Delete statistics of closed periods” (code 006) — it physically deletes old documents and reduces the load on the transaction log.

Cause-and-effect relationship:
the less historical data → the fewer active transactions → the more effectively .ldf shrinks.

Step 2. Shrinking the log file (Shrink)

After internal cleanup, perform a physical reduction of the file:

1. Connect to SQL Server via SQL Server Management Studio (SSMS).

2. Select the required database (usually TorgSoftDB).

3. Right-click → Tasks → Shrink → Files.

4. File type: Log.

5. Run the shrink procedure.

Result:
SQL Server frees the occupied disk space without data corruption.

Specifics for SQL Server Express

In the free edition:

• .mdf has a hard limit:

• 10 GB — SQL Server 2008 R2 and newer.

• 4 GB — SQL Server 2005.

• .ldf has no formal limit, but:

• if the disk becomes full, SQL Server cannot operate,

• any operations (including Torgsoft updates) may fail.

If the database is already in Suspect state

If the database switched to Suspect or Recovery Pending:

• standard Shrink will not work,

• you must first bring the database out of the emergency state using special SQL commands,

• only then can the log be shrunk.

In this case, self-service actions without experience are not recommended.

Emergency recovery when .ldf is missing or damaged (risk of data loss)

Applies only when:

• the database does not start,

• the .ldf file is missing or damaged,

• recovery by standard methods is not possible,

• you have current backups or have accepted the possibility of partial data loss.

Sequence of actions:

1. Stop the SQL Server service.

2. Delete the transaction log file (.ldf).

3. Start SQL Server.

4. In SQL Server Management Studio run:

USE master

GO

sp_configure 'allow updates', 1

RECONFIGURE WITH OVERRIDE

GO

ALTER DATABASE <db_name> SET EMERGENCY, SINGLE_USER

DBCC CHECKDB ('<db_name>', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE <db_name> SET ONLINE, MULTI_USER

USE master

GO

sp_configure 'allow updates', 0

GO

Notes:

• The REPAIR_ALLOW_DATA_LOSS option allows partial data loss.

• This method is not used for planned .ldf reduction.

• After recovery, you must check database integrity and perform a full backup.

Prevention (recommended)

To prevent the log from growing again:

• regularly run:
  “Rebuild and reorganize indexes and update statistics”,

• monitor free space on the system disk,

• avoid operating with the disk almost full,

• enable backup:
  “Data security: cloud archive” (code 057).

• pay attention to Autogrowth. In SSMS database settings, it is better to set a limit on log file growth (for example, up to 5–10 GB) so it cannot physically consume all disk space and cause an operating system crash.

Summary

Growth of .ldf is a symptom, not an error.
Correct sequence:

1. clean internal logs,

2. remove outdated statistics,

3. physically shrink the log,

4. perform regular prevention.

This approach helps stabilize the system without the risk of data loss and recurrence.