July 1, 2008 at 9:45 am
I run this script weekly from SQL-Server to check the database structure. The web application that uses this database should be bounced off by the "SET SINGLE". It has run without error for several months, until last week when it failed. It did not change back to multi-user. No errors were found.
ALTER DATABASE CLICK_ETL SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('CLICK_ETL', repair_rebuild)
ALTER DATABASE CLICK_ETL SET multi_USER
What might have gone wrong? Nothing useful in the logs. I am using SQL-Server 2000 SP4 with all hot fixes applied on Windows Server 2003 SP2.
Thank you for any help.
July 1, 2008 at 11:48 am
Try running DBCC CHECKDB ('CLICK_ETL', repair_rebuild) in query analyzer and check for errors or if it is scheduled as an job then specify job log file and check that file once job completes its execution.
Manu
July 1, 2008 at 11:56 am
maybe another connection got in before this job so it could not connect to set back to single.
I would also question why do this in the first place, why run repair options if you don't even know if you have got any corruptions yet?
---------------------------------------------------------------------
July 1, 2008 at 1:08 pm
I run the repair_rebuild both to find errors and to correct minor problems, and rebuild indexes, without risk of data loss, accorind to the BOL. At 4 AM Sunday when it is run our users are not on the system. With a 14 GB database with several hundred tables it runs in about 5 minutes.
July 1, 2008 at 2:39 pm
I would still run the checkdb without the repair option, will save having to put it into single user mode and thus require downtime. checkdb is not particualrly intrusive in sql 2000.
if errors are found that require repairs, then put it into single user and run repair.
If it aint broke, don't try to fix it..................
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply