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
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!
