September 27, 2006 at 9:06 pm
Hi. We're running SQL 2000 SP3. We had a severe slowdown in db performance that appears to be caused by the SQL log reader job.
We run sp_blocker (which gathers info related to blocking and blocked spids, spids waiting on a resource, wait stats, and what those SPIDS were running at the time) every minute to help us troubleshoot issues. The evidence from sp_blocker implicates the log reader job.
The sp_blocker output shows that dozens of spids trying to do "COMMITS" were waiting with a waittype of 0x0404 (LATCH_EX, which is waiting for an exclusive latch to do I/O, presumably to write to the TLOG?). The output also shows that the log reader was blocking the SQL job that cleans the distribution database.
The db ground to a halt for about 20 minutes. When we stopped the log reader job, db performance immediately went back to normal.
Unfortunately, I don't have OS logs that would show activity and queues on the TLOG disk drive.
Has anyone seen similar performance issues related to the log reader job?
Thanks in advance,
Bruce
October 2, 2006 at 8:00 am
This was removed by the editor as SPAM
October 3, 2006 at 11:25 am
Large Transactions in the Log produce these effects.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply