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!