CORRUPTION 101

Category: Miscellaneous, SQL Corruption, Uncategorized Comments: 0

CORRUPTION TOOLKIT:

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

Preparation
• Snapshot / locks
• Emergency mode

Critical Info
• Allocation bitmap checks
• Storage Engine system tables
• System tables

User Data
• User tables
• Index checks
• Data Purity

Special Checks
• 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

Analysis phase
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.

TROUBLE-SHOOT RECOVERY

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

Phases of DBCC CHECK

Leave a Reply Cancel reply

Request For Proposal