Introduction

Microsoft SQL Server keep logs of all transactions performed on databases. These logs grow over time and without proper maintenance can lead to space issues since SQL Server allocates a set amount of space to the transaction log.

QC-CALC Real-Time checks the transaction log size and will display an error like this if it finds the log is full.

Number: -2146233088

Description: The transaction log for database 'qc_calc' is full due to 'LOG_BACKUP' and the holdup lsn is (11:12345:67).

The database name 'qc_calc' will instead be the name of your database, and the "holdup lsn" will refer to a specific log sequence number (lsn) in your transaction log.

How to Check Transaction Log Disk Usage

To check the space usage of your transaction log, you can either run a report or a SQL query:

Print a Disk Usage Report:

1. In SSMS, expand the Databases folder
2. Right-click on your QC-CALC database, choose Report > Standard Reports > Disk Usage
3. A report like in the example below will be displayed

SQL Query to see the log size:

SELECT file_id, type_desc,

CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,

CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,

CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,

CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb

FROM sys.database_files;

Shrink a Transaction Log

You can shrink the transaction log in your SQL database by using SSMS:

1. In SSMS, expand the Databases folder
2. Right-click on your QC-CALC database and choose Tasks > Shrink > Files
3. In the next window you must:
1. Set File Type to "Log"
2. Set the Shrink action to "Reorganize pages before releasing unused space"
3. Specify the size in MB to shrink the transaction log to (0 MB is the minimum).

As an example, the database below had a log file of around 32 MB before shrinking:

After shrinking the log file according to the steps above, the log file size was reduced to less than 1 MB.

Transaction Log Autogrowth Policies

You can query SQL to set the growth policy on your transaction log:

ALTER DATABASE [DatabaseName]

MODIFY FILE(NAME = 'DatabaseName_log',SIZE = 200MB,FILEGROWTH = 1MB)

That query will alter the database DatabaseName and set the SIZE and FILEGROWTH options for the transaction log file named DatabaseName_Log. Specifically, it sets the log size to 200 MB and enables it to grow 1 MB at a time when the size grows beyond 200 MB.

See Microsoft's article for more information on this topic:

https://learn.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-ver17#3-change-log-size-limit-or-enable-autogrow