SQL Server Performance Forum – Threads Archive
Integrity check job failsHi Following are the list of jobs i have in all my servers. 1)DB backup of all user databases
2)Integrity check for all user databases
3)Optimization for all user databases
4)Transactional log backup for all user databases 1)DB backup used to fail and i rempved -BkUpOnlyIfClean -CkDBRepair options from query and now its working fine
2)But Integrity check Jobalways fails in most of the servers .This also has same
-CkDBRepair option. How to rectify this error ?Following is the query used by job . EXECUTE master.dbo.xp_sqlmaint N’-PlanID 9EF657F7-764B-4AD5-A292-993D7CE3CA9A -Rpt "f:MSSQLLOGKbo944a_systemdb_maintenance2.txt" -DelTxtRpt 1WEEKS -WriteHistory -CkDBRepair ‘ pls advice .
What error does it give? Also, change your job to write the output of the job to OS file. Sometimes this file is very helpful in debugging. HTH
It gives folowing error for all the DB .  Database master: Check Data and Index Linkage…
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. The following errors were found: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8579 – I think you’re repeating the old problem. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
Yes Satya, its repetition.<br /><br />*I have removed integrity chek along with backupjob now Backup job works fine.<br />*Integrity check job has -CkDBRepair and i changed to -CkDB , now it works fine.But what is the use in just checking the database without repairing <img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /> . Microsoft also recommends not to use repair option .<br /><br />I didnt find anywhere solution to this issue
The point is to not include the "Attempt to repair minor problems" in your maintenance plan since it will try and repair the error right at the spot when it finds it. And most likely that won’t work since it will want to put the database in single user mode when you have users in it. Instead let it just check for errors without repair. And if the integrity check job fail and reports it found errors then you go in and run DBCC CHECKDB etc in a controlled way on the database.