6/12/2011 5:41:32 AM
Vasudev Menon -
During the ongoing SQL migration on our TESTING environment cluster, things were not progressing as per plan. Initially the EVA SAN LUN(s) movement from source to target cluster node(s) gave some major headaches. Solving our way through it took up a major amount of time.
Actual SQL DB migration was already running late, when we were hit by a replication failure. Now during the closing stages after resolving all issues, in the late night comes the bad news. One of our DBA team mates escalates with a dejected face saying “a db xSxxxxxD has gone into a suspect mode. Please help me as I am tired and am not able to think”.
Why is this issue not easily recoverable?
Moral of the story: Before you start a database migration, it pays well to ensure that the overnight “FULL BACKUP(s)” have been successfully taken.
Vasudev Menon (Member since: 4/26/2011 5:00:57 AM)
View Vasudev Menon 's profile
Execute DBCC CHECKDB (xSxxxxxD, REPAIR_ALLOW_DATA_LOSS). This would take this own sweet time.
Are you sure that the above is advisable because all the rows in a corrupt page will simply be deleted which can lead to data loss.
I have said 2 important things in the blog
1. "best option out is a restore"
2. The moral of the story "Always ensure a full backup is in place before starting migration".
When a DBA faces an issue and troubleshoots, there are many options available and specially when he writes a blog to the wider community he needs to put across all options available, so that when others face a similar issue they are aware of the best and the worst options.
Now lets say:
a. What if the poor chap had no backup? He just sits back and pray?
b. Should he not attempt to bring the DB online with the next available options on hand? mark my words, I have said next available option.
Now to your question of data loss:
Purely in the context of "this blog" and "the error I was troubleshooting", I had clearly mentioned it is the non-clustered index that had gone kaput.
You seriously think carring out DBCC checktable or checkdb with DATA_LOSS for a non-clustered index, will result in a data loss... from the table object itself?
Oh sorry I had missed that you had mentioned that error message indicated an issue with the index ,thats why all the confusion.
No problem Sachin, please do feel free to always question. That is the only way, by questioning, can a healthy discussion on the pros and cons of a topic be initiated and knowledge shared among the wider community.
Nice Post Vasu
Sir, Is work with and use of DBCC CHECKDB (xSxxxxxD, REPAIR_ALLOW_DATA_LOSS) command data will loss or not...and if yes the how much chances to LOSS data and is there any other Step or process for Repaitr Suspect Database in SQL SERVER 2005\2008
This post really helped me, thanks Vasu.
Hey Sachin, it seems that you are very much prompt on every blogs and all, well carry on the nice work and at the same time do read the blogs Carefully.. ;)
Well that is what a job of a moderator is I guess..
Leave a comment