I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query:
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
But the repair output was this message:
Warning: You must recover this database prior to access. Msg 8921, Level 16, State 1, Line 5 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors. Warning: The log for database 'ServeDB' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. Msg 8921, Level 16, State 1, Line 9 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
How to repair the database fully? This repair which i made, works only for some days, then database again goes to Suspect mode...