Distribution Clean Up error

• Eudes

  SSC-Addicted

  Points: 420

  More actions

  July 27, 2010 at 2:04 pm

  #94994

  I have several publications in SQL 2000, when my distribution clean up job runs it shows me the error:

  Executed as user: domain\user The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration. [SQLSTATE HY000] (Error 1204). The step failed.

  Table MSrepl_commands had 10 million records.

  I did a manual wipe and now has 100,000 records and still continues with the above error.

  can anybody help me

  thanks.

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  August 3, 2010 at 11:35 am

  #1201706

  There is setting that can be done using sp_reconfigure to mitigate this issue.

  USE master

  EXEC sp_configure 'show advanced options', 1

  GO

  RECONFIGURE WITH OVERRIDE

  GO

  EXEC sp_configure 'locks', '0'

  GO

  RECONFIGURE WITH OVERRIDE

  GO

  Check this out and let me know. It is one of the shortcuts used to fix this issue.

  -Roy