Hi Faisal,
We had the same problem last month, we were able to bring back the DB to normal status by following the steps given below. Try it out, it may work out for you.
Here are the steps on how to try to fix the corruption.
1) create one database with name of "mytestdb". The database file should point to the same as the user database. For example,
F:\Program Files\Microsoft SQL Server\MSSQL$xyz\Data
2) Offline SQL server.
3) rename mytestdb.mdf to mytestdb.mdf.bak. Rename your userdatabase.mdf to mytestdb.mdf. userdatabase.mdf is the name of the user database MDF file.
4) Online SQL server. Now mytestdb may be in suspect mode.
5) run the below script to put mytestdb to emergency mode:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
update sysdatabases set status = 32768 where name = 'mytestdb'
6) offline and online SQL server again.
7) rebuild the log for mytestdb:
DBCC TRACEON (3604)
DBCC REBUILD_LOG('mytestdb','mytestlog1.ldf')
8) Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption 'mytestdb', 'single user', 'true'
DBCC CHECKDB('<db_name>')
Go
Faisal Qureshi <mssqldba-ezmlmshield-x69027281.[Email address protected] wrote:
Hi Guys
Our Production database became Suspect couple of nights ago. Microsoft has not been able to help out in this situation. Their recommendation was to restore from backup, however that didn't work either. Any pointers would be appreciated.
About the database:
Its a 750 GB database, with Three Filegroups (Primary, Secondary, Tertiary). All the filegroups have ONE datafile in them. All of separate physical drives. The log file is yet on another physical drive. DB is running on a cluster, with EMC backup.
According to the Server Logs, around midnight, the sql server service restarted and it could not recover this production database, and marked it suspect.
We've tried the following with no success.
1. after making sure that all the files are physically there, we tried to reset the status, restart the SQL Server service, but the server was not able to recover the database, haenced it was marked suspect again.. The error that we were getting was
"Failed Assertion" and according to MS online search, that error could occur if MSDB options are changed manually (which obviously was not the case in our situation)..
2. We created a new dummy database with same file structure and filegroups and gave the filenames as *_new.mdf, *_new.ndf, *_new.ndf & *_new.ldf (in the same locations as the original database. Files were 1 GB each (log file 10 MB).
Then we took the new database offline, renamed the files of the Original production database to the new file names (after renaming them to old), tried to restart SQL Service, but when it tried to restore the database, gave a strange error that MS was not able to explain either.. it gave the filename (with path) of the *.NDF files, saying, this is not a primary file.. (actual message is soemthing like).
"Error 5171: J:\apps\data\db_name_S1_data_new.NDF is not a primary database file. Device activation error. The physical filename may be incorrect..
same error for the file for Tertiary Filegroup. MS was also puzzled that WHY its looking at the *.NDF as primary, while the Primary file is already recognized.
It seems like in the sysfiles table, somehow the Original PRoduction server is showing up as with ONE primary file and ONE Log file (as opposed to ONE primary, TWO secondary and ONE log file).
3. Finally we decided to restore from backup. Since EMC took a backup (scheduled) around 1am, of the corrupt Databsase, we had to restore from Tape.
The tape restore finished this morning, but the database is still suspect. When we reset teh status using sp_resetstatus, it came up with the same error in #2 above.
Any quick help would be greatly appreciated. I've tried to explain things briefly but tried to cover all aspects we've been struggling with for the past two days. but if someone has some idea and need more specific info, please respond and i'll try to give that info.
At this point we need to recover the database, even with a 5 day old backup, since we can re-populate the data (it would be painful, but doable).. please advise.
Thanks
Faisal
---------------------------------
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger
MS Sql Server LazyDBA home page