SQL Server Performance Forum – Threads Archive

Distribution Cleanup Agent Deadlock

Hi all, I have a problem with my distibution database where the clean up agent keeps failing because of a deadlock issue. This is causing the database to become very large. Does anyone know of anything obvious that it may deadlock with? I’d rather not run any trace flags because the machine is under a lot of load as it is – but if I have to then I have to. I know that the server is short on memory. I have a page life exp of < 100 seconds though the cache hit ratio is still good at 98%. However my distribution agents often fail with buffer latch timeouts on the distribution database. This disk is also very busy – I think that the memory shortage is partly to do with this. I only have transcational replication and no anonymous subscribers. The server is a 2003 Ent Cluster runing SQL 2000 SP3. Thanks for any help in advance! Simon

If your initial assumption is towards memory shortage then I suggest to addup more physical memory and if its standard edition of SQL then you may not have much joy no more than 2GB. With regard to buffer latch timeout error, as long as SQL Server carries on, and no other errors are being reported, then there is usually nothing to worry about. They are merely showing that the disk subsystem is unable to keep up with the level of i/o that SQL Server is asking it to do. I feel SYSMON (PERFMON_) during this process will help to assess further resolution.
I presume SQL is equipped with latest SP. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>