Fix SQL Server Errors 3313 & 3314: Repair Page Allocation and Corruption Issues

 

Introduction

SQL Server Error 3313: During redoing of a logged operation in database '<database_name>', an error occurred at log record ID (X:Y:Z) and Error 3314: During undoing of a logged operation in database '<database_name>', an error occurred at log record ID (X:Y:Z) indicate issues with page allocation or corruption, often occurring during transaction log replay (redo or undo phases) when SQL Server starts or recovers a database. These errors typically result from corrupted transaction logs, data pages, or disk issues, rendering the database inaccessible or in a suspect state. This guide provides code-oriented solutions to diagnose and repair Errors 3313 and 3314 using DBCC CHECKDB, backups, and other recovery strategies.

---

1. Diagnose the Issue with Error Logs

Review SQL Server and Windows Event Logs to identify the cause of the corruption or allocation issue.

Code-Oriented Solution

Query the SQL Server error log:

-- Read error log for Errors 3313 and 3314
EXEC xp_readerrorlog 0, 1, N'3313', NULL, NULL, NULL, N'asc';
EXEC xp_readerrorlog 0, 1, N'3314', NULL, NULL, NULL, N'asc';

Check the Windows Event Log for disk or I/O errors:

# Check Windows Event Log for related errors
Get-WinEvent -LogName System | Where-Object { $_.Id -in (7, 51, 55) } | 
Select-Object TimeCreated, Id, Message | Format-Table -AutoSize

Action:

• Review the SQL Server error log (located at C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\ERRORLOG) for details about the affected database, log record ID, or specific pages.

• Note any disk or I/O errors in the Windows Event Log that may indicate hardware issues.

• Identify the database and transaction log file involved.

Why? Errors 3313 and 3314 are often caused by transaction log corruption or disk issues during log replay, and logs provide critical diagnostic information.

---

2. Check Database and File Status

Verify the database state and file accessibility to confirm the scope of the issue.

Code-Oriented Solution

Check database state:

-- View database state
SELECT name, state_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

Check database file details:

-- View file paths and states
SELECT 
    name AS FileName,
    physical_name AS FilePath,
    type_desc AS FileType,
    state_desc AS FileState
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabaseName');

Verify file accessibility with PowerShell:

# Check if files exist
Test-Path -Path "C:\SQLData\YourDatabase.mdf"
Test-Path -Path "C:\SQLData\YourDatabase_Log.ldf"

Action:

• If the database is in a SUSPECT or RECOVERY_PENDING state, proceed to repair or recovery steps.

• Ensure the .mdf and .ldf files exist and are accessible.

• Check file permissions for the SQL Server service account:

# Grant full control to SQL Server service account
$acl = Get-Acl -Path "C:\SQLData\YourDatabase_Log.ldf"
$permission = "NT Service\MSSQLSERVER","FullControl","Allow"
$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($permission)
$acl.SetAccessRule($accessRule)
Set-Acl -Path "C:\SQLData\YourDatabase_Log.ldf" -AclObject $acl
Set-Acl -Path "C:\SQLData\YourDatabase.mdf" -AclObject $acl

Why? Errors 3313 and 3314 can occur if the transaction log or data files are inaccessible or corrupted, causing recovery failures.

---

3. Run DBCC CHECKDB to Assess Corruption

Use DBCC CHECKDB to diagnose the extent of corruption in the database.

Code-Oriented Solution

Run DBCC CHECKDB to identify corruption:

-- Run DBCC CHECKDB
USE YourDatabaseName;
GO
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;

If the database is inaccessible, run in emergency mode:

-- Set database to emergency mode
ALTER DATABASE YourDatabaseName SET EMERGENCY;

-- Run DBCC CHECKDB
DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Action:

• Review the output for details about corrupted pages, tables, or log issues.

• Save the output for reference:

-- Save DBCC CHECKDB output
DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS
INTO YourDatabaseName_CheckDB_Output;

• If specific tables are affected, check them individually:

-- Check a specific table
DBCC CHECKTABLE ('YourTableName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Why? Errors 3313 and 3314 often involve page or log corruption, and DBCC CHECKDB pinpoints the affected objects.

---

4. Backup the Database Before Repair

Create a full and tail-log backup to preserve data before attempting repairs.

Code-Oriented Solution

Backup the database:

-- Full database backup
BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak'
WITH INIT;

-- Tail-log backup (if in FULL recovery model)
BACKUP LOG YourDatabaseName
TO DISK = 'C:\SQLBackups\YourDatabaseName_TailLog.trn'
WITH NO_TRUNCATE;

Action:

• Verify the backup:

RESTORE VERIFYONLY
FROM DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak';

• Store backups securely to avoid overwriting.

Why? Backups ensure data can be restored if repairs cause data loss.

---

5. Repair Corruption with DBCC CHECKDB

Attempt to repair the corruption using DBCC CHECKDB, but note that some repairs may result in data loss.

Code-Oriented Solution

Set the database to single-user mode and repair:

-- Set database to single-user mode
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Attempt repair without data loss
DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- If REPAIR_REBUILD fails, try repair with data loss (use cautiously)
DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Return to multi-user mode
ALTER DATABASE YourDatabaseName SET MULTI_USER;

Action:

• Start with REPAIR_REBUILD for non-data-loss fixes (e.g., index corruption).

• Use REPAIR_ALLOW_DATA_LOSS only as a last resort, as it may delete corrupted pages or data.

• Re-run DBCC CHECKDB to confirm repairs:

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Why? DBCC CHECKDB with repair options can fix page allocation or corruption issues, but REPAIR_ALLOW_DATA_LOSS risks data loss.

---

6. Rebuild the Transaction Log

If the transaction log is corrupted, rebuild it to resolve log-related errors.

Code-Oriented Solution

Rebuild the transaction log:

-- Set database to emergency mode
ALTER DATABASE YourDatabaseName SET EMERGENCY;

-- Rebuild log file
ALTER DATABASE YourDatabaseName SET SINGLE_USER;
DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE YourDatabaseName REBUILD LOG ON (
    NAME = 'YourDatabase_Log',
    FILENAME = 'C:\SQLData\YourDatabase_Log.ldf'
);
ALTER DATABASE YourDatabaseName SET MULTI_USER;

Action:

• Verify the database is online:

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

• Run DBCC CHECKDB to ensure consistency:

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS;

Why? Errors 3313 and 3314 often involve transaction log corruption, and rebuilding the log can restore database access, though it may cause data loss.

---

7. Restore from Backup

If repairs are not feasible or cause significant data loss, restore the database from a backup.

Code-Oriented Solution

Restore the database and apply transaction logs:

-- Restore full backup
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak'
WITH NORECOVERY;

-- Restore transaction log backup
RESTORE LOG YourDatabaseName
FROM DISK = 'C:\SQLBackups\YourDatabaseName_TailLog.trn'
WITH RECOVERY;

Action:

• Verify backups before restoring:

RESTORE VERIFYONLY
FROM DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak';

• If no log backup exists, attempt to extract data from the corrupted database:

-- Export data from a corrupted table
SELECT * INTO NewTable
FROM YourCorruptedTable
WHERE 1 = 1; -- Use WHERE to filter out corrupted rows if possible

• Verify the restored database:

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS;

Why? Restoring from a backup is the safest recovery method, minimizing data loss compared to repairs.

---

8. Investigate Disk and Hardware Issues

Check for underlying disk or hardware issues causing corruption or allocation errors.

Code-Oriented Solution

Check for disk errors in the Windows Event Log:

# View disk-related errors
Get-WinEvent -LogName System | Where-Object { $_.Id -in (7, 51, 55) } | 
Select-Object TimeCreated, Id, Message | Format-Table -AutoSize

Check disk health:

# Run disk check
chkdsk C: /f /r

Action:

• If disk errors are found, move database files to a reliable disk:

-- Detach database
EXEC sp_detach_db 'YourDatabaseName';

-- Move files to new disk (manually via file system)

-- Re-attach database
EXEC sp_attach_db 
    @dbname = 'YourDatabaseName',
    @filename1 = 'D:\NewSQLData\YourDatabase.mdf',
    @filename2 = 'D:\NewSQLData\YourDatabase_Log.ldf';

• Contact your system administrator to check RAID controllers, SAN storage, or hardware issues.

• Monitor disk space:

# Check disk space
Get-Disk | Get-Partition | Get-Volume | Select-Object DriveLetter, SizeRemaining, Size

Why? Errors 3313 and 3314 can be triggered by disk failures or I/O issues affecting data or log files.

---

9. Test Database Access

Test database operations to confirm resolution.

Code-Oriented Solution

Test with sqlcmd:

# Test database access
sqlcmd -S sqlserverhostname -U YourLoginName -P YourPassword -d YourDatabaseName -Q "SELECT TOP 1 * FROM YourTableName"

In SSMS, run a test query:

-- Test query
SELECT TOP 1 * FROM YourTableName;

Action:

• If errors persist, recheck DBCC CHECKDB output or restore from a backup.

• Update application code to handle corruption errors:

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=YourDatabaseName;Integrated Security=True;";
try {
    using (SqlConnection conn = new SqlConnection(connString)) {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 * FROM YourTableName", conn)) {
            using (SqlDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    Console.WriteLine("Data retrieved successfully!");
                }
            }
        }
    }
} catch (SqlException ex) when (ex.Number == 3313 || ex.Number == 3314) {
    Console.WriteLine($"Error {ex.Number}: Page allocation or corruption issue. Run DBCC CHECKDB.");
} catch (Exception ex) {
    Console.WriteLine($"Error: {ex.Message}");
}

Why? Testing confirms the database is accessible and corruption is resolved.

---

10. Implement Prevention Strategies

Set up maintenance to prevent future corruption or allocation issues.

Code-Oriented Solution

Schedule regular DBCC CHECKDB:

-- Create SQL Server Agent job for DBCC CHECKDB
USE msdb;
GO
EXEC sp_add_job @job_name = 'DBCC_CheckDB_Weekly';
EXEC sp_add_jobstep 
    @job_name = 'DBCC_CheckDB_Weekly',
    @step_name = 'Run_DBCC_CHECKDB',
    @subsystem = 'TSQL',
    @command = 'DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS;';
EXEC sp_add_jobschedule 
    @job_name = 'DBCC_CheckDB_Weekly',
    @name = 'WeeklyCheck',
    @freq_type = 8, -- Weekly
    @freq_interval = 1, -- Sunday
    @active_start_time = 20000; -- 2:00 AM

Enable page verification:

-- Enable page checksums
ALTER DATABASE YourDatabaseName
SET PAGE_VERIFY CHECKSUM;

Schedule regular backups:

-- Schedule full and log backups
BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak'
WITH INIT;

BACKUP LOG YourDatabaseName
TO DISK = 'C:\SQLBackups\YourDatabaseName_Log.trn'
WITH INIT;

Action:

• Schedule weekly DBCC CHECKDB runs to detect corruption early.

• Monitor disk health and space regularly.

• Set up alerts for Errors 3313 and 3314:

-- Create alerts
EXEC msdb.dbo.sp_add_alert 
    @name = N'Page Corruption Error 3313',
    @message_id = 3313,
    @severity = 0,
    @enabled = 1,
    @notification_message = N'Error 3313: Page allocation/corruption issue. Run DBCC CHECKDB.',
    @include_event_description_in = 1;

EXEC msdb.dbo.sp_add_notification 
    @alert_name = N'Page Corruption Error 3313',
    @operator_name = N'YourOperatorName',
    @notification_method = 1; -- Email

EXEC msdb.dbo.sp_add_alert 
    @name = N'Page Corruption Error 3314',
    @message_id = 3314,
    @severity = 0,
    @enabled = 1,
    @notification_message = N'Error 3314: Page allocation/corruption issue. Run DBCC CHECKDB.',
    @include_event_description_in = 1;

EXEC msdb.dbo.sp_add_notification 
    @alert_name = N'Page Corruption Error 3314',
    @operator_name = N'YourOperatorName',
    @notification_method = 1;

Why? Proactive maintenance, backups, and monitoring reduce the risk of page allocation and corruption issues.

---

Conclusion

SQL Server Errors 3313 and 3314 (page allocation or corruption) can be resolved by diagnosing with DBCC CHECKDB, repairing corruption, rebuilding the transaction log, or restoring from backups. Address underlying disk or hardware issues to prevent recurrence. Use the provided T-SQL, PowerShell, and C# examples to diagnose and fix these issues systematically. 

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone