Link to home
Start Free TrialLog in
Avatar of ps224
ps224

asked on

SQL Server 2012 Error 22022

SQL Server 2012 Error 22022


 Hi,
 
I am getting Error 22022 while running job from SQL Agent. Job contains maintenance plan for taking database backups. backups are not done from last 3 days. but the second part of maintenance plan, for log backups is running fine i.e  happening every hour. Below is the error i get once i restart the sql agent and run the job manually

===================================

Start failed for Job 'B01-BI Databases Backup.Full'.  (Microsoft.SqlServer.Smo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
   at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ProgressItemCollection actions, Int32 index)
   at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.Agent.Job.StartImpl(String jobStepName)
   at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()

===================================

SQLServerAgent Error: Request to run job B01-BI Databases Backup.Full (from User sa) refused because the job is already running from a request by User sa. (.Net SqlClient Data Provider)



------------------------------
Server Name: B01\BI
Error Number: 22022
Severity: 16
State: 1

Please suggest the solution.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ps224
ps224

ASKER

As i have checked the db processes, there was once process (maintenance Plan), prior to backup step, running the DBCC check db on master database (check table with suspended status) for very long time(started on 3rd May)  And other WeeklyMaintenancePlan.Subplan_1 was also running for cleaning purpose. so i had to kill both processes.  Now I have restarted the SQL Agent service. Because Reboot requires proper plan so i think is not a good idea.
Avatar of ps224

ASKER

Hi,

Jobs still stuck in doing DBCC for the database id 19, which doesn't exist in database list. and couldn't run the backup for DBs through Maintenance plan. I have attached the sysprocesses for that spid.
Sysprocesses.xlsx
Avatar of ps224

ASKER

Now I have finally removed the Integrity check part for DBs prior to backups step from Maintenance plan. Now able to take backups.

But One strange thing is that, there are total 18 DBs on instance including system DBs, than why DBCC command was running on dbid 19 as shown in sysprocesses.
Avatar of ps224

ASKER

Hi Experts,

Please close this question

Thanks
Hi ps224,
  the responsibility to close/delete the question is yours
a3
Avatar of ps224

ASKER

Thanks experts, this solution partially worked for me.
Hi ps224,
  the responsibility to close/delete the question is yours
a3