Sql-server – Maintenance Job Failing

maintenance-planssql server

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…

Best Answer

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.

Related Solutions

Sql-server – Changed server name, now maintenance plan fails

You need to change the owner of the maintenance plan. It was set up to be owned by an account that no longer exists. Not sure if it is better in your scenario to use sa or the new local admin account. This update assumes you still have the sqladmin account after the rename and that it has been added as a login to SQL Server with admin privileges.

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE ownersid = SUSER_SID('WIN-6QFI9JAK804\sqladmin');

You may also need to reset the owner of the job associated with the maintenance plan, which you can do through the UI or via the following:

EXEC msdb..sp_update_job
  @job_name = N'MaintenancePlan.Subplan_1', -- or whatever it's called
  @owner_login_name = N'VM-MSSQL-2008R2\sqladmin';

If the login hasn't been added, then execute this first:

CREATE LOGIN [VM-MSSQL-2008R2\sqladmin] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'VM-MSSQL-2008R2\sqladmin', N'sysadmin';

In the future, be sure to have the machine properly configured / named etc. before installing SQL Server. :-)

SQL Server – Maintenance Plan Job Running at Unscheduled Times

Well, mystery solved.

I had previously posted this at another site (SqlServerCentral.com), and then posted here after I had not gotten a response for several hours. While I was trying Aaron's suggestion here, a Josh posted the following there:

Did you check the history of the job to see how it was invoked? Usually you'll see something like "The job succeeded. The Job was invoked by Schedule XXXX (Schedule_X ). The last step to run was step 1 (XXXXXXXXXXXXXX)."

Does it look the same for each run?

Though I had previously scoured the Job's history, I had not specifically looked for that message. And lo and behold, the unscheduled ones have the following:

... The Job was invoked by Alert 1.  ...

So someone must have tagged this job to run after an Alert (likely the wrong job).

Naturally now I feel silly for not checking this before. But I post this here now, so that others in the future may benefit from my oversight.

Best Answer

Related Solutions

Sql-server – Changed server name, now maintenance plan fails

SQL Server – Maintenance Plan Job Running at Unscheduled Times

Related Question