Unfortunately the SQL 2000 Maintenance Plan error messages are generic. When the job fails the details for the error are hidden and all you get is the generic 22029 error. Below are steps I’ve used when troubleshooting these errors. You may find this technique works for other SQL Agent errors.
Best way to determine underlying error is to run the same maintenance plan JOB from a CMD window. To do this, copy the JOB properties to your favorite text editor or use trusty Notepad.exe.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID D9A91AB6-9D8E-40B3-9D11-625747E19FC5 -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan26.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -BkExt "TRN"'
Remove the xp_sqlmaint call and add the sqlmain.exe. Also remove the N’ in the beginning and the trailing ‘ (single quote). It should end up like:
C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlmain.exe -PlanID D9A91AB6-9D8E-40B3-9D11-625747E19FC5 -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan26.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -BkExt "TRN"
Next open a CMD window and paste in this cmd string. It best if you are logged onto the SQL Server using the same account as the SQL Server Agent account. After all, this is the account running the JOB.
In my example, the CMD window displayed the results of every action. Most ran correctly, but as you can see the Transaction Log backup of MDB failed which caused the Maintenance Plan job to fail with the generic error 22029.
Turns out I had tried to create a Transaction Log backup job on the MSDB database which was set for SIMPLE RECOVERY (full backup only), and not FULL RECOVERY (full and log backups). Once I excluded MSDB Database from the Tran Log backup maintenance plan it completed without any errors.
Most often SQL Agent jobs are not this easy to track down. Often you’ll encounter a permissions/authority problem. For example, the SQL Agent account doesn’t have WRITE access to the file share storing the database backups. That’s why it is easier to track these down while logged on as the same account used by SQL Agent.
Microsoft SQL Server Escalation Services