This can happen following things like hardware failure, power outages, database files being locked by the Operating System (Anti-Virus, backup software etc.) or actual corruption of the database.
Attempting the repair procedure below is really a last resort. If you have good and recent backups, then if at all possible I would perform point-in-time recovery of the database concerned, as this emergency repair can (as the name suggests) result in data loss.
If you’ve gone through all of this like I once did though, and all other avenues failed, here are the steps that fixed it for me:
EXEC sp_resetstatus 'YourDBName' ALTER DATABASE 'YourDBName' SET EMERGENCY
(You won’t be able to query the database until it’s in EMERGENCY mode!)
DBCC CHECKDB ('YourDBName') ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CHECKDB ('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE YourDBName SET MULTI_USER
In the end, I just had to rebuild a couple of indexes after the repair process and everything was recovered….phew!
ALTER INDEX ALL ON [YourTableName] REBUILD
Again, it’s worth stressing that this is a last resort – use a good backup/standby etc. first, if you have one!