Error in maintenance plan history cleanup task (exec msdb.dbo.sp_delete_backuphistory)

• Database admin(DBA)

  SSCertifiable

  Points: 5373

  More actions

  December 11, 2013 at 1:36 am

  #395968

  Hello Friends,

  I got one Strange Error

  "Executing the query "declare @dt datetime select @dt = cast(N'2013-10-11T14:40:53' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt

  " failed with the following error: "The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

  The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.". Possible failure reasons: Problems with the query,

  "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

  i was tried DBCC opentran(No Active transactions) and sys.dm_os_waiting_tasks and tran_locks also but no useful.

  Environment Sql server 2005 ram Total 3GB SQL is 2.5GB Allocated for Sql server and i.e.,currently Log Shipping Primary database server .

  Kindly help me on this ...

  Thanks in advance.

  Thanks& Regards,

  DBA

  SQL server DBA