1. Understanding the architecture of the problem

Saint Administrative in its SQL version uses a two-layer architecture: the application developed in Delphi (Object Pascal) that communicates with Microsoft SQL Server as a database engine. This architecture generates constant transactions that are recorded in the transaction log.

What is the transaction log?

The file .ldf (Log Data File) is critical for data recovery. Each INSERT, UPDATE, or DELETE operation generates records in this file before it is confirmed in the main database. In the recovery model full (default in business installations), these logs are kept until a log backup is made.

Causes of uncontrolled growth

• Long transactions without commit: Delphi batch processes that are not terminated correctly
• Lack of log backups: Without periodic truncation, the log grows indefinitely
• MASSIVE INQUIRIES: Historical reports that generate millions of log records
• Failed replication: Interrupted sync processes that leave transactions open
• integration processes: Data imports from Excel or other systems without transaction control

2. Immediate diagnosis

Before acting, it is crucial to identify the current state of the log and what transactions are consuming space.

Script 1: Verify space used by the log

-- Verify percentage of transaction log usage
DBCC SqlPerf(logspace)
GO

-- Alternative: Detailed query by database
select 
db.name Ace [Base de Datos], ,
db.recovery_model_desc Ace [Modelo Recuperación], ,
ls.cntr_value / 1024.0 Ace [Tamaño Log MB], ,
lu.cntr_value Ace [Porcentaje Usado %], ,
    case 
        when lu.cntr_value > 90 then Critical
        when lu.cntr_value > 70 then Warning
        else Normal
    end Ace [Estado]
from sys.databases db
Inner Join sys.dm_os_performance_counters lu
    on db.name = lu.instance_name
Inner Join sys.dm_os_performance_counters ls
    on db.name = ls.instance_name
where lu.counter_name = Percent log used
    and ls.counter_name = log file(s) size (kb)
    and db.name = Saint

Script 2: Identify open transactions

-- Detect transactions that keep the log open
select 
st.session_id,
st.open_transaction_count,
    DateDiff(d)minute, st.last_request_start_time, GetDate()) Ace [Minutos Activos], ,
ST.STATUS,
St.Command,
    db_name(st.database_id) Ace [Base de Datos], ,
st.host_name,
st.program_name,
st.login_name
from sys.dm_exec_sessions st
where st.open_transaction_count > 0
    and db_name(st.database_id) = Saint
order by st.last_request_start_time

Script 3: Verify disk space

-- Verify available disk space on the server
EXEC xp_fixeddrives
GO

-- Physical location of files from the Saint Database
select 
name Ace [Nombre Lógico], ,
physical_name Ace [Ruta Física], ,
Size * 8 / 1024 Ace [Tamaño Actual MB], ,
max_size * 8 / 1024 Ace [Tamaño Máximo MB], ,
    case Growth
        when 0 then Fixed
        else Autogrow + cast(Growth * 8 / 1024 as varchar) + mb
    end Ace [Crecimiento]
from sys.master_files
where db_name(database_id) = Saint

3. Emergency solution

When the sql server is stopped or the database is in “Susspect” or “Recovery Pending” mode, follow these steps in order:

Step 1: Free immediate space (simple model)

If the company can tolerate point-time recovery loss, temporarily switching to the simple model truncates the log:

-- Switch to simple model (truncates the log immediately)
alter database Saint Simple recovery set
GO

-- Force write pending data to disk
checkpoint
GO

-- Reduce the log file to 1 GB (adjust as needed)
DBCC Shrinkfile (Saint_Log, 1024)
GO

-- Check new size
DBCC SqlPerf(logspace)

Step 2: Free space (Full model – recommended)

If it is required to keep the retrieval capacity to point in time:

-- Backup of the transaction log (truncates already backed parts)
backup log Saint
to disk = D:\backups\saint_log_emergency.trn
with compression, stats = 10
GO

-- Verify that the backup is complete
Restore VerifyOnly from disk = D:\backups\saint_log_emergency.trn
GO

-- Reduce the physical file
DBCC Shrinkfile (Saint_Log, 1024)
GO

Step 3: Identify and terminate blocking processes

If there are Delphi transactions that keep the log open:

-- Identify the problematic SPID (session ID)
select 
session_id,
open_transaction_count,
    DateDiff(d)minute, last_request_start_time, GetDate()) Ace minutes_active,
status,
command
from sys.dm_exec_sessions
where open_transaction_count > 0
    and DateDiff(d)minute, last_request_start_time, GetDate()) > 30

-- If it is safe to end the session (replace xx with session_id):
kill xx

-- Verify that space was released
DBCC SqlPerf(logspace)

Step 4: Verify Post-Recovery Integrity

-- Verify database logical and physical integrity
DBCC CHECKDB (d)Saint) with no_infomsgs
GO

-- Verify database status
select name, state_desc, recovery_model_desc
from sys.databases
where name = Saint

4. Preventive configuration

To prevent the problem from repeating, implement the following automated maintenance plan using SQL Server Agent:

Job 1: Log backup every 15 minutes

-- Create Frequent Log Backup Job
use MSDB
GO

EXEC sp_add_job
@Job_Name = saint_backup_log_15min, ,
@enabled = 1,
@description = Saint's transaction log backup every 15 minutes
GO

EXEC sp_add_jobstep
@Job_Name = saint_backup_log_15min, ,
@Step_Name = backup log, ,
@subsystem = tsql, ,
@command = backup log Saint to disk =D:\backups\logs\saint_log_$(escape_squote(strtdt))_$(escape_squote(STRTTM)).trnwith compression, retaindays = 2, ,
@Database_Name = master's degree
GO

EXEC sp_add_schedule
@Schedule_Name = every_15_minutes, ,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 15,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_time = 0
GO

Job 2: Space Monitoring and Alert

-- Create alert when the log exceeds 80%
EXEC sp_add_alert
@Name = saint_log_space_critical, ,
@message_id = 0,
@severity = 0,
@enabled = 1,
@delay_between_responses = 300,
@include_event_description_in = 1,
@Database_Name = Saint, ,
@performance_condition = databases|percent log used|saint|>|80, ,
@Job_Name = SAINT_NOTIFY_ADMINISTRATOR
GO

Job 3: Index Maintenance (Weekly)

-- Fragmented index rebuild (reduce log operations)
execute dbo.indexoptimize
@Databases = Saint, ,
@fragmentationLow = null, ,
@FragmentationMedium = index_reorganize,index_rebuild_online, ,
@fragmentationhigh = index_rebuild_online,index_rebuild_offline, ,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = And

Summary of key commands