SQL Server Performance Forum – Threads Archive

Resetting the Suspect Status

I have tried to use tips found under ‘Resetting the Suspect Status’ topic in BOL. But it didn’t help. Everytime I restart SQL Server, one database goes into suspect mode. Is there anything else I can do?
Can I find it somewhere in BOL? — Marek Grzenkowicz

Check: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4074 HTH Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

First of all checkup why this particular database is marked as suspect, review data & log files location drives for any errors – event viewer for information. BTW, any reason why the SQL server is restarted frequently. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

quote:Originally posted by satya First of all checkup why this particular database is marked as suspect, review data & log files location drives for any errors – event viewer for information.

I found an information about an unexpected system restart in Event log.
And there is an information about a torn page in SQL Server log.

quote:Originally posted by satya BTW, any reason why the SQL server is restarted frequently.

I meant the server restarts during my attempts to perform database disaster recovery. (Which, BTW, failed, but fortunately there was a backup.) But anyway. What I really wanted to ask you is – is emergency mode documented in BOL? — Marek Grzenkowicz

True the Emergency mode is not fully documented or referred in BOL, only values are referred. Torn page detection is done whenever a page is read from disk. In practice, this is likely to be during recovery, because any page on which the write did not complete during normal operations is very likely to be read by recovery (except for non-logged operations, such as index creation, bcp, and so on). So it sounds the database file drive has got some issues, may check with vendor or Network admin. To overcome the situation restoring the database from a backup and rolling the transaction log forward should correct the problem with no data loss. I presume you will be on latest service packs on SQL & OS. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <pre> In case it causes at our end, I use: Sp_configure "allow updates", 1 Reconfigure with override GO Update sysdatabases set status = -32768 where name = "BadDbName" Sp_configure "allow updates", 0 Reconfigure with override GO </pre>[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

<blockquote id="quote">quote:<hr height="1" id="quote" noshade="noshade">Originally posted by satya To overcome the situation restoring the database from a backup and rolling the transaction log forward should correct the problem with no data loss. <hr height="1" id="quote" noshade="noshade"></blockquote> That’s what I did. <blockquote id="quote">quote:<hr height="1" id="quote" noshade="noshade">Originally posted by satya I presume you will be on latest service packs on SQL & OS. [<img src="http://sql-server-performance.com/community/emoticons/emotion-1.gif" alt="

">] <hr height="1" id="quote" noshade="noshade"></blockquote> Yes. Always. Am I right in thinking that MS SQL Server was rather designed with a user, always having backups and restoring from them, in mind and not one wanting to fix a ‘broken’ database? – Marek Grzenkowicz

In theory it looks like with the procedures to be followed. So did you find any information for that h/w mismatch or corruption which is contributing to this database suspect error?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

quote:Originally posted by satya So did you find any information for that h/w mismatch or corruption which is contributing to this database suspect error?

It took me some time to find what ‘h/w’ is. Now I know. I did not handle this problem to the end, but AFAIK the server restart occured while SQL Server was writing data to the disk and then again when it was trying to restore the database after the first restart. And this caused the problem. So it had nothing to do with h/w. And before you ask — I was not the one who was restarting SQL Server machine like this. — Marek Grzenkowicz

Ah, many questions answered in one shot. No more to say [<img src=’/community/emoticons/emotion-5.gif’ alt=’

]]>