The last thing to try before restore when you have a SQL Server 823 Error / OS Error 21 corruption

Short Version

Take database Offline and Online again. Do at your own risk.

Long Version

  • SAN Based Storage , Disks / LUNs disappeared from Windows / Reappeared again within about 1 minute
  • No Errors in SQL Server when this happened
  • 12 hours later 823 / Error 21s start appearing when SQL is being used more.
  • Narrow down corruption down to 2 File/Filegroups , 1 Volume (around 10 volumes / 15 Filegroups)
  • Problem files around 400 Gbytes in Size / 300 Gbytes used.
  • All other CHECKFILEGROUPs come back clean
  • CHECKFILEGROUP on problem filegroups fails instantly with error 823 / Latch error
  • Any SELECT statement against problem Filegroups data comes back with similar 823 / OS 21 Error.
  • Able to create New 10 Gbyte DB / Log file on alleged problem volume.
  • Able to write 100Gbytes / read fine to alleged problem volume by doing backup / verify only of another db (data on different volume with no issues). Showed that SQL Server had no general problems accessing / writing / reading to it
  • Was just considering moving problem files to different volume , to see if problem was being caused by a problem with single volume.
  • Thought would try offline / online as the 21 = “Device not ready” , suggested that SQL Server was having problems with these particular .ndf’s / thought there was a problem with disk
  • To my surprise db recovered successfully , half expected it to go suspect.
  • CHECKFILEGROUP on problem files now run for expected time / clean
  • CHECKDB on whole DB Clean
  • Backup of DB / VerifyOnly Clean
  • SQL Server 2008 R2 SP1 CU5 / Windows Server 2003

Follow up

  • Obviously find root cause
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s