SQL Server Q&A Topic 14: Indexing & Query Optimization Scenarios

SQL Server Interview Questions & Answers

Topic 14: Indexing & Query Optimization Scenarios  

Questions 121–130 | Advanced Scenario-Based Questions

This section covers real-world SQL Server transaction log scenarios including unexpected log growth, log-full conditions, active transactions, Availability Group log retention, recovery-model changes, large DELETE operations, disk-space emergencies and Virtual Log File management.

The most important principle when troubleshooting transaction log problems is to identify why SQL Server cannot reuse existing log space before attempting to shrink the log or make other configuration changes.

121. The transaction log has grown to 500 GB unexpectedly. How would you determine the cause?

Answer:
I would not immediately shrink the transaction log. First, I would determine why SQL Server needed 500 GB and why existing log space could not be reused.

My first check would be:

SELECT
    name,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

I would then investigate:

• Missing or failed transaction log backups
• Long-running transactions
• Large INSERT, UPDATE or DELETE operations
• Index rebuilds
• Availability Group synchronization problems
• Replication-related log retention
• Large bulk operations
• Unexpected workload growth
• Log autogrowth history

I would also check current log usage:

SELECT
    total_log_size_in_bytes / 1024.0 / 1024.0 AS TotalLogSizeMB,
    used_log_space_in_bytes / 1024.0 / 1024.0 AS UsedLogSpaceMB,
    used_log_space_in_percent AS UsedLogSpacePercent
FROM sys.dm_db_log_space_usage;

Senior DBA Tip:
A 500 GB log file is not necessarily a problem if the workload genuinely requires that space. The important question is whether the growth was expected, reusable and appropriately sized.

122. The database transaction log is full even though log backups run every 15 minutes. What would you check?

Answer:
Log backups running every 15 minutes do not guarantee that log space can always be reused.

I would first check:

SELECT
    name,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

Then I would verify:

• Are the log backups actually succeeding?
• Is a transaction preventing log truncation?
• Is an Availability Group replica behind?
• Is replication preventing log reuse?
• Is a backup or restore operation active?
• Has workload volume suddenly increased?
• Has the transaction log reached its configured maximum size?
• Is the disk itself full?

I would also confirm backup history rather than assuming the scheduled SQL Server Agent job is successful:

SELECT TOP (20)
    database_name,
    backup_start_date,
    backup_finish_date,
    type
FROM msdb.dbo.backupset
WHERE database_name = 'YourDatabaseName'
  AND type = 'L'
ORDER BY backup_finish_date DESC;

Interview Tip:
"We run log backups every 15 minutes" describes a schedule. It does not prove that the backups succeeded or that another condition is not preventing log truncation.

123. log_reuse_wait_desc reports ACTIVE_TRANSACTION. How would you troubleshoot it?

Answer:
ACTIVE_TRANSACTION indicates that an active transaction is preventing SQL Server from reusing part of the transaction log.

A useful first check is:

DBCC OPENTRAN;

I would then identify active transactions and associated sessions.

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_tran_session_transactions AS st

INNER JOIN sys.dm_tran_active_transactions AS at
    ON st.transaction_id = at.transaction_id

INNER JOIN sys.dm_exec_sessions AS s
    ON st.session_id = s.session_id

ORDER BY
    at.transaction_begin_time;

I would investigate:

• How long the transaction has been open
• Which application/session owns it
• Whether it is actively working or idle
• How much log it is consuming
• Whether the application forgot to COMMIT or ROLLBACK
• The impact of terminating the session

Production Warning:
Do not immediately KILL a large transaction. Rolling it back may itself take considerable time and can continue generating operational impact. Understand the rollback implications first.

124. log_reuse_wait_desc reports AVAILABILITY_REPLICA. What does this indicate?

Answer:
AVAILABILITY_REPLICA indicates that Availability Group processing is preventing transaction log truncation on the primary.

This commonly occurs when a secondary replica is unable to process log records quickly enough or is disconnected.

I would check:

• Replica connectivity
• Synchronization state
• Synchronization health
• Log send queue
• Redo queue
• Network latency
• Secondary storage performance
• Secondary replica CPU and memory pressure
• Whether the secondary database is suspended

Example:

SELECT
    DB_NAME(drs.database_id) AS DatabaseName,
    ar.replica_server_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.log_send_queue_size AS LogSendQueueKB,
    drs.redo_queue_size AS RedoQueueKB
FROM sys.dm_hadr_database_replica_states AS drs

INNER JOIN sys.availability_replicas AS ar
    ON drs.replica_id = ar.replica_id

WHERE drs.is_local = 1;

Senior DBA Tip:
If the primary log is growing because of AVAILABILITY_REPLICA, repeatedly shrinking the primary log does nothing to solve the real problem. Investigate the AG data movement path and secondary health.

125. A developer asks you to shrink the transaction log every night. How would you respond?

Answer:
I would explain that routine transaction-log shrinking is not a recommended maintenance strategy.

If the normal workload requires a 100 GB log, shrinking it to 10 GB every night simply creates this cycle:

100 GB Log
    ↓
Shrink to 10 GB
    ↓
Production workload starts
    ↓
Log grows again
    ↓
Growth overhead
    ↓
Back to 100 GB
    ↓
Shrink again

That creates unnecessary:

• Autogrowth activity
• Storage overhead
• Possible workload pauses during growth
• Poor VLF distribution over time

I would instead:

1. Determine the normal peak log requirement.
2. Pre-size the transaction log appropriately.
3. Use a sensible fixed autogrowth increment.
4. Monitor unexpected growth.
5. Shrink only after an exceptional event when the excess space is genuinely no longer required.

Interview Tip:
If the log repeatedly grows back to the same size after shrinking, that is strong evidence the workload needs approximately that amount of log space.

126. A database was accidentally changed from Full to Simple recovery and then back to Full. What is the impact?

Answer:
Changing from Full recovery to Simple interrupts the existing transaction-log backup recovery sequence.

Changing the database back to Full does not simply reconnect the old log-backup sequence.

I would:

1. Confirm the current recovery model.
2. Document the time during which the database was in Simple recovery.
3. Assess the recovery exposure against the business RPO.
4. Take an appropriate full or differential data backup to establish the new recovery sequence.
5. Resume transaction log backups.
6. Confirm backup jobs are succeeding.

Example:

ALTER DATABASE SalesDB
SET RECOVERY FULL;

BACKUP DATABASE SalesDB
TO DISK = 'E:\SQLBackup\SalesDB_FULL.bak'
WITH COMPRESSION,
     CHECKSUM;

Production Tip:
Recovery-model changes should always be treated as recovery-design changes, not merely database configuration changes.

127. A database in Full recovery has never had a log backup. What behavior would you expect?

Answer:
A database configured for Full recovery does not necessarily begin operating with a useful Full-recovery log backup chain immediately.

The recovery sequence first needs to be established with an appropriate data backup.

Once the database is operating in the Full recovery model with the recovery sequence established, regular transaction log backups are necessary for normal log management and point-in-time recovery.

If log backups are then not taken, inactive log records required for the log-backup chain cannot normally be truncated simply by taking additional full database backups.

I would verify:

• Recovery model
• Whether a recovery sequence has been established
• Last full backup
• Last log backup
• log_reuse_wait_desc
• Current transaction log usage

If the business requires point-in-time recovery, I would implement transaction log backups according to the required RPO.

Interview Tip:
Do not assume that selecting Full recovery alone provides point-in-time recovery. The backup strategy must support it.

128. A large DELETE operation causes massive transaction log growth. How could you handle the operation more safely?

Answer:
A single DELETE affecting millions of rows can generate a very large transaction, consume substantial log space, hold locks for an extended period and create a large rollback requirement if the operation fails.

Where business logic allows, I would consider processing the deletion in controlled batches.

Example:

WHILE 1 = 1
BEGIN

    DELETE TOP (10000)
    FROM dbo.AuditHistory
    WHERE CreatedDate < '2025-01-01';

    IF @@ROWCOUNT = 0
        BREAK;

END;

I would also:

• Ensure the predicate is properly indexed where appropriate
• Estimate the number of affected rows
• Check available transaction-log capacity
• Check disk space
• Confirm log backups are running where applicable
• Monitor AG lag if Availability Groups are involved
• Consider scheduling during lower business activity
• Measure batch size and transaction duration

For very large data-retention operations on correctly designed partitioned tables, partition switching or partition-level management may sometimes be preferable to row-by-row deletion.

Senior DBA Tip:
Choose the batch size based on workload behavior rather than assuming 10,000 rows is universally correct. Monitor transaction duration, blocking, log generation and replication/AG impact.

129. The log drive is almost full during a critical production transaction. What actions would you consider?

Answer:
This is an operational emergency. My immediate objective would be to prevent the database from losing the ability to generate transaction log records while avoiding actions that create a larger outage.

I would quickly determine:

• Current disk free space
• Current log usage
• log_reuse_wait_desc
• Active transaction size and duration
• Whether log backups are succeeding
• Availability Group health
• Whether additional storage can be provisioned

Possible actions, depending on the root cause, include:

1. Free unrelated disk space safely if possible.
2. Extend the storage volume if infrastructure supports it.
3. Take the required log backup if LOG_BACKUP is the actual reuse wait.
4. Resolve an AG or replication condition if it is responsible for retention.
5. Assess whether continuing or rolling back the active transaction is safer.
6. As an emergency workaround, consider adding an additional log file on another suitable volume if the existing volume cannot be expanded.

Example emergency workaround:

ALTER DATABASE SalesDB
ADD LOG FILE
(
    NAME = SalesDB_Log2,
    FILENAME = 'F:\SQLLogs\SalesDB_Log2.ldf',
    SIZE = 10240MB,
    FILEGROWTH = 2048MB
);

I would treat the additional log file as an emergency capacity action, not a routine performance configuration.

Production Warning:
Never switch a production database from Full to Simple recovery merely to solve a full-disk incident without understanding and accepting the recovery consequences.

130. You notice thousands of VLFs in a transaction log. What problems can this cause and how would you correct it?

Answer:
VLFs, or Virtual Log Files, are internal logical segments inside the physical SQL Server transaction log file.

A very high VLF count can historically be associated with:

• Longer crash-recovery processing
• Longer database startup or recovery in some scenarios
• Additional log-management overhead
• Backup and restore overhead in some workloads

Excessive VLF counts are commonly associated with transaction logs that grew through many small autogrowth events.

Check VLFs using:

SELECT
    file_id,
    vlf_begin_offset,
    vlf_size_mb,
    vlf_sequence_number,
    vlf_active,
    vlf_status
FROM sys.dm_db_log_info(DB_ID())
ORDER BY
    file_id,
    vlf_begin_offset;

To see the count:

SELECT
    COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID());

If corrective action is genuinely required, I would plan it carefully:

1. Determine the normal transaction-log size required by the workload.
2. Ensure log truncation is functioning correctly.
3. Reduce the file only when operationally appropriate.
4. Regrow or pre-size the log deliberately to its required steady-state size.
5. Configure a sensible fixed autogrowth increment.
6. Monitor future VLF growth and log capacity.

DBA Tip:
There is no universal VLF count that automatically requires corrective action. Consider SQL Server version, database size, recovery behavior and actual operational impact.

Useful DBA Script: Transaction Log Health Check

SELECT
    d.name AS DatabaseName,
    d.recovery_model_desc AS RecoveryModel,
    d.log_reuse_wait_desc AS LogReuseWait,
    mf.name AS LogicalLogName,
    mf.physical_name AS LogFile,
    mf.size * 8.0 / 1024 AS LogSizeMB,
    CASE
        WHEN mf.max_size = -1
            THEN 'Unlimited'
        ELSE
            CAST(mf.max_size * 8.0 / 1024 AS varchar(30)) + ' MB'
    END AS MaxSize,
    CASE
        WHEN mf.is_percent_growth = 1
            THEN CAST(mf.growth AS varchar(20)) + '%'
        ELSE
            CAST(mf.growth * 8.0 / 1024 AS varchar(20)) + ' MB'
    END AS AutoGrowth
FROM sys.databases AS d

INNER JOIN sys.master_files AS mf
    ON d.database_id = mf.database_id

WHERE mf.type_desc = 'LOG'

ORDER BY d.name;

Useful DBA Script: Last Transaction Log Backup

SELECT
    d.name AS DatabaseName,
    MAX(bs.backup_finish_date) AS LastLogBackup
FROM sys.databases AS d

LEFT JOIN msdb.dbo.backupset AS bs
    ON d.name = bs.database_name
    AND bs.type = 'L'

GROUP BY d.name

ORDER BY d.name;

Useful DBA Script: Identify Active Transactions

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    at.transaction_begin_time,
    DATEDIFF
    (
        SECOND,
        at.transaction_begin_time,
        GETDATE()
    ) AS TransactionAgeSeconds,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_tran_session_transactions AS st

INNER JOIN sys.dm_tran_active_transactions AS at
    ON st.transaction_id = at.transaction_id

INNER JOIN sys.dm_exec_sessions AS s
    ON st.session_id = s.session_id

ORDER BY at.transaction_begin_time;

Transaction Log Troubleshooting Flow

Transaction Log Growing / Full
   ↓
Check Disk Free Space
   ↓
Check Current Log Usage
   ↓
Check log_reuse_wait_desc
   ↓
LOG_BACKUP?
ACTIVE_TRANSACTION?
AVAILABILITY_REPLICA?
REPLICATION?
Other Wait?
   ↓
Fix Root Cause
   ↓
Determine Normal Peak Log Requirement
   ↓
Pre-Size Log Appropriately
   ↓
Configure Fixed Autogrowth
   ↓
Review VLF Distribution
   ↓
Shrink Only If Excess Space Is Truly Unneeded

Senior DBA Transaction Log Principle

When the transaction log grows, do not start with SHRINK.

Start with:

1. Why did SQL Server need more log space?
2. Why can the existing log space not be reused?
3. Is the growth temporary or part of the normal workload?
4. Is the backup/recovery strategy correct?
5. Is HA/DR retaining log records?
6. What is the correct steady-state log size?

Fix the cause first. Size the transaction log second. Shrink only when justified.

Interview Quick Recap

• Unexpected transaction-log growth should be investigated through log_reuse_wait_desc before shrinking the file.
• Scheduled log backups do not guarantee successful log truncation.
• ACTIVE_TRANSACTION usually requires investigation into long-running or open transactions.
• AVAILABILITY_REPLICA points toward Availability Group data-movement or secondary-replica conditions.
• Routine transaction-log shrinking creates unnecessary shrink-grow cycles.
• Changing from Full to Simple recovery interrupts the existing transaction-log recovery sequence.
• Full recovery alone does not provide point-in-time recovery without an appropriate backup strategy.
• Large DELETE operations can often be made safer using controlled batches.
• A nearly full log disk requires root-cause investigation and capacity action, not random recovery-model changes.
• Excessive VLF counts are often associated with repeated small transaction-log growth events.

← Previous Topic
# Topic 13: Indexing & Query Optimization Scenarios

Next Topic →

# Topic 15: Backup, Restore & Disaster Recovery Scenarios

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning