SQl2005 "suspect" database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQl2005 "suspect" database

Dear all, Apologies if this is an obvious one, but has been many years since I’ve worked with SQL and I have an issue at the same time that the guy who normally looks after it is off sick. It appears that the database Indexes have reached their maximum allowed size (through lack of maintenance plans working) and this has caused the database to load as Suspect. I’ve tried to do some research on this and used the sp_configure (allow updates), and sp_resetstatus both of which appear to work at the time but make no difference, and I am thinking that this is because the indexes are out of space. There is ample free space on the drive that holds the indexes (200GB+), but I don’t know how to enlarge the space allowed for the indexes while it is in this state. Could anyone advise please? Thanks in advance,
Nick
Did you try:
EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER


Hi Luis, Many thanks, and I ended up dropping the log file entirely and rebuilding, all looks ok now. Best wishes for the new year Nick
Best wishes for you too.:)
Yeah Luis, its amazing the scripts we collect over the years, i had that one in my handy scripts directory already but good to have BEFORE your need it!:)
;)
Yeah Luis, its amazing the scripts we collect over the years, i had that one in my handy scripts directory already but good to have BEFORE your need it!:)
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |