- BACKUP & RESTORE – See if the backup is clean once you restore (usually on another server.
- BCP (Bulk Copy) & DB ATTACH – try to BCP remove the corrupt tables and restore (attach) the remaining database
- REBUILD/REPAIR databases
System database issues (corruption) – rebuild will replace system database then you can replace with a backup.
- SQLIOSIM – located in binn folder. Can detect disk subsystem concerns/issues.
- TRACE FLAGS – troubleshoot to determine which database are corrupted/issues
- DBCC CHECK – can determine the consistency of the database/files and find corruptions
If you are running repair make sure to make a copy of database to another folder. This will ensure the you can go back to the original state. Make sure to stop SQL server first.
Phases of DBCC CHECK
• Snapshot / locks
• Emergency mode
• Allocation bitmap checks
• Storage Engine system tables
• System tables
• User tables
• Index checks
• Data Purity
• Service Broker
• Indexed Views & XML
• Catalog consistency – shows broken links between system tables. Possibly if we
have a stored procedure that has a broken link with (IDs) with system tables.
It will show us clearly.
--THIS WILL SHOW THE ERRORS AND REPAIR OPTION(S) DBCC CHECKDB (Test_DB) WITH ALL_ERRORMSGS
--HOW TO REPAIR: --Set to single user mode ALTER DATABASE Test_DB SET SINGLE_USER --Run repair option on database DBCC CHECKDB ( Test_DB, repair_rebuild ) WITH ALL_ERRORMSGS --This will tell you if it is successful or not. Set back to multi-user ALTER DATABASE Test_DB SET MULTI_USER
If it is a large Database >200GB to make sure you can get the output you can create a scheduled job (without interruption and save the full output.
Database Startup Problems
If the failed database comes into Suspect phase:
SQL can’t proceed. It can’t access the database.
You can recover from a backup. Or, put db in emergency mode. This will allow you to view the data, but not input data.
Or run CHECKDB REPAIR command.
If there is a resource problem then “Recovery Pending”
SELECT * FROM sys.dm_os_waiting_tasks FROM session ID = ??
If you see a negative number these are system Sessions created to prevent from moving forward.
Recovery, Redo, undo phase
DBCC CHECKDB (TEST_DB, REPAIR_ALLOW_DATA_LOSS )
This will start new Transaction log and remove any transactions that were happening so this is a last resource… A repair like this can de-allocate or remove one page of information (8KB) of data.
Defer errors during recovery
Problem: Recovery encounters page errors (824,829) or offline FG (3410) Can we defer) : If database is “Deferred” it can still be recovery can still be completed.
Run sys.databases : This will show you the state of the databases
How many VLF’s does your system have?
DBCC LOGINFO ( 'Test_DB' )
How many rows affected = # of VLFs
FileSize = VLF Size
FileID = .l
StartOffset = what position it starts at
Status = Active (2) or Inactive (0). Only way to take it from Active to Inactive is to take a log backup
If recovery is slow be sure to check the log to see how quickly it is being restored.
Building VLFs were previously being built very slowly in the past. There are patches to improve this – KB 2455009
Common corruption errors
823 – OS Error – can’t get the content to read from the disk.
824 Logical Consistency – got the content from the disk, but it isn’t the same as when we wrote it.
5242 – Physical row consistency
9004 – Log corruption – sometimes problems with transaction log
3456 – Log record consistency – inconsistency with applying log
How to troubleshoot a Msg 823 error in SQL Server
How to troubleshoot Msg 824 in SQL Server
How to troubleshoot Msg 825 (read retry) in SQL Server
How to troubleshoot Msg 832 (constant page has changed) in SQL Server
How to troubleshoot database consistency errors reported by DBCC CHECKB
How to troubleshoot Error 3414 and a failed database recovery with SQL Server
How to troubleshoot Error 17204 and 17207 in SQL Server
How to troubleshoot Error 9004 in SQL Server
How to troubleshoot Msg 7105 in SQL Server
How to troubleshoot Msg 5180 in SQL Server
How to troubleshoot Msg 605 with SQL Server