1

I didn't realize but it appears you can put switches on your job to run a maintenance plan. Am I correct on that?

We have 3 jobs that are running the same maintenance plan but doing different things with it.

The first two jobs succeed, and the 3rd job fails. Here are the jobs .I cannot figure out why that 3rd job fails. I was able to find a table in msdb that shows that plan ID, and all three of these jobs have the same plan id, so I know it uses the same maintenance plan.

    The code for the three of them are:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -RebldIdx 100 '
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -VrfyBackup -BkUpMedia DISK -BkUpDB "F:\Backup" -DelBkUps 1DAYS -BkExt "BAK"'
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -CkDBRepair  '

The first two pass, and the 3rd one is the one that fails…

  • what is the error message from the plan history (not the job history) for that part? – Kevin3NF Feb 22 '17 at 0:54
  • And what SQL version? – Kevin3NF Feb 22 '17 at 1:21
  • Error log is sqlstate42000 Error 22029 – user117000 Feb 22 '17 at 21:04
  • This is sql 2000. So I have found out that if you click "attempt to repair", it tries to put the database into single-user mode. Is there anything that would stop it from going into single user mode and therefore cause the job to fail? I have set the maintenance plan up to output to a file on sunday when it runs again – user117000 Feb 23 '17 at 14:26
  • Other connections not releasing...setting to SINGLE_User by default will wait until there is only 1 IIRC. Sorry my SQL 2000 is very rusty :) – Kevin3NF Feb 23 '17 at 14:33
1

One thing to note is that xp_sqlmaint is scheduled to removed in the future and is not the recommended method for running the maintenance plans. (Though it is still supported at this time.)

For SQL Server maintenance I would recommend downloading the Ola Hallengren solution at https://ola.hallengren.com/. This is a freely provided product for managing the integrity checks as well as, if you wish, to automate your database backups. And Ola is quick to respond with personal help.

These are highly respected scripts, which I have been using for several years.

You seem to have a corruption problem if the -CkDBRepair cannot finish the check. If that is the case, then you will need to be careful in how you fix the problem so as to avoid as much loss as possible. So please carefully read:

DBCC CHECKDB (Transact-SQL)

Be sure to read the warnings on DBCC CHECKDB and the impact of allowing data loss when resolving the problem.

  • Yikes, I didn't want to hear that. I will have a look into this and get back...uh oh. – user117000 Feb 22 '17 at 20:45
0

"For the REPAIR_FAST option to work, the DB needs to be in single user mode. SQLMaint does not put the database in single user mode before running the CHECKDB. Nor does SQLMaint return an error when the DBCC command fails to execute."

https://expertreplies.com/fix-sqlmaint-exe-ckdbrepair-option-does-not-work-correctly/

Not sure how valid this post is...as I cannot test it.

  • So the DB is never being put into single user mode which we wouldn't want anyhow. Would that cause the job to fail? If the job tried to run and it wasn't in single user mode, and in the maintenance plan, that option is saying it needs to be, would that be an automatic failure right there? If I look in the sql logs, it is showing DBCC Checked as succeeding. I'm not sure what this is all telling me. – user117000 Feb 23 '17 at 14:53
  • I suspect only if it finds a problem. This is all I know, and some of it is educated guessing :) – Kevin3NF Feb 23 '17 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.