Database marked SUSPECT problem (service won't start)

Database marked SUSPECT problem (service won't start)

Post by Mike » Wed, 06 Dec 2000 04:00:00



Our system crashed during a power-failure and after the reboot, the SQL
Server service fails. It throws an eventlog:

17052 : Database 'model' cannot be opened. It has been marked SUSPECT by
recovery. See the SQL Server errorlog for more information.

The Online books says to run the the stored procedure to manually reset the
state from "SUSPECT", but this only works if the service is running!! So how
can I restart my SQL Service without being automatically stopped again.
Help!

Thanks in advance,
Mike

 
 
 

Database marked SUSPECT problem (service won't start)

Post by Gary S » Wed, 06 Dec 2000 04:00:00


I believe you can start SQL Server in single user mode:   sqlservr -m

Go into Enterprise Management (or MMC) and in the master database look for
the sysdatabases table. I think you might need to change the status field to
1 for the 'model' database.

Then run the DBCC CHECKDB command.

Good Luck!
- Gary


Quote:> Our system crashed during a power-failure and after the reboot, the SQL
> Server service fails. It throws an eventlog:

> 17052 : Database 'model' cannot be opened. It has been marked SUSPECT by
> recovery. See the SQL Server errorlog for more information.

> The Online books says to run the the stored procedure to manually reset
the
> state from "SUSPECT", but this only works if the service is running!! So
how
> can I restart my SQL Service without being automatically stopped again.
> Help!

> Thanks in advance,
> Mike


 
 
 

Database marked SUSPECT problem (service won't start)

Post by Mike » Thu, 07 Dec 2000 16:56:22



Quote:> I believe you can start SQL Server in single user mode:   sqlservr -m

> Go into Enterprise Management (or MMC) and in the master database look for
> the sysdatabases table. I think you might need to change the status field
to
> 1 for the 'model' database.

> Then run the DBCC CHECKDB command.

> Good Luck!
> - Gary

Gary, thanks for your reply. Unfortunately, SQL service stops immediately
after it notices the SUSPECT database, so I cannot use my Enterprise
Manager, since there is no connection with the SQL Server.

Where can I find the DBCC and CHECKDB commands? Couldn't find it in the BINN
folder.

Thanks again,
Mike

 
 
 

Database marked SUSPECT problem (service won't start)

Post by Tibor Karasz » Thu, 07 Dec 2000 04:00:00


There exists a trace flag with which you don't do recovery on databases:

Do not recover any database
 -T3607

Do not recover any database except master
 -T3608

Unfortunately, there don't seem to be documented anymore in KB....
--
Tibor Karaszi, SQL Server MVP
Neil's FAQ at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.




> > I believe you can start SQL Server in single user mode:   sqlservr -m

> > Go into Enterprise Management (or MMC) and in the master database look for
> > the sysdatabases table. I think you might need to change the status field
> to
> > 1 for the 'model' database.

> > Then run the DBCC CHECKDB command.

> > Good Luck!
> > - Gary

> Gary, thanks for your reply. Unfortunately, SQL service stops immediately
> after it notices the SUSPECT database, so I cannot use my Enterprise
> Manager, since there is no connection with the SQL Server.

> Where can I find the DBCC and CHECKDB commands? Couldn't find it in the BINN
> folder.

> Thanks again,
> Mike

 
 
 

Database marked SUSPECT problem (service won't start)

Post by Mike » Thu, 07 Dec 2000 04:00:00




Quote:> There exists a trace flag with which you don't do recovery on databases:

> Do not recover any database
>  -T3607

> Do not recover any database except master
>  -T3608

> Unfortunately, there don't seem to be documented anymore in KB....

I guess this is going to be a fresh install of SQL Server :(
Maybe I can try SQL2000 :-)
 
 
 

Database marked SUSPECT problem (service won't start)

Post by Tibor Karasz » Thu, 07 Dec 2000 04:00:00


I'd do some searching around for documents describing those trace flags. Possible
search strings are:
3607
3608
t3607
t3608

--
Tibor Karaszi, SQL Server MVP
Neil's FAQ at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.




> > There exists a trace flag with which you don't do recovery on databases:

> > Do not recover any database
> >  -T3607

> > Do not recover any database except master
> >  -T3608

> > Unfortunately, there don't seem to be documented anymore in KB....

> I guess this is going to be a fresh install of SQL Server :(
> Maybe I can try SQL2000 :-)