April 7, 2010 at 7:22 pm
I'm trying to attach a database using the wizard and I'm getting an error message:
---------------------------
Microsoft SQL-DMO (ODBC SQLState: HY000)
---------------------------
Error 7987: A possible database consistency problem has been detected on database 'XXXX'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'XXXX'.
---------------------------
OK
---------------------------
I'd love to run the commands it tells me to run, but since I am unable to attach the database, I am unable to run these commands. How do I get past this error in order to attach?
April 7, 2010 at 9:55 pm
I was able to fix this by following the steps below:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
b. Get the value of the status column in sysdatabase table
select * from sysdatabases where name = 'db_name'
-- note the value of the status column for later use in # 6
begin tran
c. Set status of the database to suspect (327768)
update sysdatabases set status = 32768 where name ='db_name'
commit tran
d. Rebuild transaction log
DBCC rebuild_log('db_name','db_log_path')
e. Set the database to single user access
sp_dboption '', 'single user', 'true'
DBCC checkdb('db_name')
Go
f. Restore the status of the database in sysdatabases table
begin tran
update sysdatabases set status = previous_value where name = 'db_name'
-- verify one row is updated before committing
commit tran
Go
g. Restore the override property of Master table
sp_configure 'allow updates', 0
reconfigure with override
Go
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply