Contention deadlocks can be a royal pain to troubleshoot. You're going to need to find out what query was being run under that PID to see what it is causing it.
A couple things here: Do you only have one application hitting that database or are there multiple? If two different applications are contending for a lock on the same table that can throw errors if not handled elegantly. What kind of transactions are you doing? I've run into this sort of problem before on a poorly designed app that had one big log table that logged info all the other tables and we had to change our transaction types to not do exclusive locks until we redesigned the application. Also, are you doing replication perchance? If it is set up to do log shipping that could explain the surge of activity on your logging disk. Judah On Mon, Feb 9, 2009 at 6:35 PM, Rick Root <rick.r...@webworksllc.com> wrote: > > SQL Server 2000 (we don't have a lot of money and the licence was > cheaper... but we are planning to upgrade sometime soon) > > Anyway.. I just noticed the drive configuration on the server is C: > and D: on one RAID array, and E: and F: on the second. Which means my > data files and OS files are all on the same set of physical disks, > probably in a mirror (I've asked the person who ordered the server to > get me the physical configuration, I'm not entirely sure how to find > that info) > > So I'm using perfmon to monitor % disk read and write on the two > physical sets of disks. > > WHENEVER there is a spike, I see the disk WRITE on the C/D drive jump > to 100% while the disk read jumps up and down between 100% and 0%... > during this time, I see disk activity on the E/F drive > > C: OS and page file > D: SQL Server Data > E: SQL Server transaction logs > F: SQL Server backups > > It can't be a coincidence that when these spikes occur are also the > ONLY time I see disk write activity to the E/F drives, where the > transaction logs are. > > ALSO... I'm seeing errors like this during the slow periods: > > Error Executing Database Query. [Macromedia][SQLServer JDBC > Driver][SQLServer]Transaction (Process ID 110) was deadlocked on lock > resources with another process and has been chosen as the deadlock > victim. Rerun the transaction. > > This kind of error message has only happened 8 times today... but > maybe it's related. I don't have a CLUE how to deal with SQL Server > deadlocks! > > > -- > Rick Root > New Brian Vander Ark Album, songs in the music player and cool behind > the scenes video at www.myspace.com/brianvanderark > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319105 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
