SQL Server Performance Forum – Threads Archive

Locking problems

Hi. I am having some locking problems on my cluster server. The database is accessed by both inside employees using customized software as well as web users who run queries, enter new info, etc. etc. I have purchased Net IQ’s SQL Diag manager, but the software has a limitation. It does not show you the actual query that caused the lock, it just informs you as to the user and basic stuff like that. Well all web users come across as " sa " so we have no idea what user or what queries are causing the file locks. We are confident that it is the website causing the locks. I am a network administrator, not a SQL DBA or a programmer, so I do not know all the ins and outs, but I am pretty knowledgeable. Does anyone know of a way I can track what queries are causing my locking problems? I would greatly appreciate any help. Thanks. My config: I have an Active/Passive cluster server environment, purchased from Dell, about $70000. I will try and give you detailed hardware: Hardware—
Dual Xeon 2.8Ghz Processors
4GB DDR-SYNCHRONOUS Memory
PERC QC RAID Controller running RAID 1, for local disks
PERC QC attached to the Dell Powervault Storage System, RAID 10 Software—
Windows 2000 Advanced Server
Microsoft SQL Server 2000 – 8.00.760 Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Microsoft Cluster Service Database details—
24GB in size ( mdf )
2400 sql transactions per minute, peak. Reported by NetIQ’s Monitoring program.

This is an article for SQL Server 7.0 but I think it’ll help you…. http://support.microsoft.com/default.aspx?scid=kb;en-us;289276 Then there is this webcast…..
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc011502/wcblurb011502.asp Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Thanks Gaurav. I was able to find the article for SQL 2000 after using the links you supplied. http://support.microsoft.com/default.aspx?scid=kb;en-us;271509 We are going to try that out and see what we come up with. Thanks.

If you know when it happens, you can use who2 in Query Analyzer to see who is locking.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

How about database maintenance checks and REINDEXING on the tables involved.
Also PROFILER is handy tool to know the queries performance cause. 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.

]]>