database marked as "Suspect"

In SEM, if a database is marked as "Suspect" and no items may be viewed,
what are the possible causes ?
Could this be caused by the hard-drive running out of space (for either the
.mdf or the .ldf files) ?
Thanks.Hi,
Various Reasons for database going to suspect Status.
1. The MDF or LDF file for the database is not available during startup
2. LDF file missing or Corrupted
3. MDF file page allocation problems.
1. The first one can be resolved by executing "sp_resetstatus" stored
procedure and restart SQL server services
2. Start the database in Emergency mode , Update the Status column in
master..sysdatabases table for that database to 32768.
After this database will be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data.
3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD
option. If the problem is not rectified
wither restore from Backup or contact Microsoft support.
How to set to Emergency Mode
--_-
Use emergency mode (also called bypass mode) to recover data when SQL Server
marks a database suspect and the automatic recovery will fail.
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Thanks
Hari
SQL Server MVP
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ucwgg2WXFHA.2776@.TK2MSFTNGP12.phx.gbl...
> In SEM, if a database is marked as "Suspect" and no items may be viewed,
> what are the possible causes ?
> Could this be caused by the hard-drive running out of space (for either
> the .mdf or the .ldf files) ?
> Thanks.
>|||> Could this be caused by the hard-drive running out of space (for either the .mdf or the .l
df
> files) ?
Shouldn't be... See url]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ucwgg2WXFHA.2776@.TK2MSFTNGP12.phx.gbl...
> In SEM, if a database is marked as "Suspect" and no items may be viewed, w
hat are the possible
> causes ?
> Could this be caused by the hard-drive running out of space (for either th
e .mdf or the .ldf
> files) ?
> Thanks.
>