Take database Offline and Online again. Do at your own risk.
- 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
- Obviously find root cause