SQL Server Performance

SQl2005 "suspect" database

Discussion in 'ALL SQL SERVER QUESTIONS' started by Nick Cox, Dec 26, 2011.

  1. Nick Cox New Member

    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
  2. Luis Martin Moderator

    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

  3. Nick Cox New Member

    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
  4. Luis Martin Moderator

    Best wishes for you too.:)
  5. MichaelB Member

    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!:)
  6. Luis Martin Moderator

    ;)

Share This Page