May 9, 2012 at 6:55 am
Hi
I have a server running 10 small databases - average 2 gig each. The databases are used by ERP and CRM applications. The applications are both client server architecture with a small number of users - approx 20 concurrent users. Occasionally there is an issue with locking / timeout on one of the databases - could be any of them, but only effects one at a time. The issue appears to be that the entire database is locked. Users currently in the application stop responding and you cannot query any of the tables in the database even if you specify nolock. The first time it happened I ran a queery to find any locking processes. There was one enquiry option that was locking all processes in the database, even those that were totally unrelated as far as the tables they would read/write. Since it was an enquiry option blocking everyone I killed it and all the locks disappeared. Unfortunately it wasn't a one off problem. I would love to find the actual cause now, however the time between occurances can be a week or two, and I can no longer queery the system tables when it happens to see what locks there are or what is the blocking process. When I run the queeries they get timed out, so does the activity monitor. If you kill off the application processes on the server the database becomes usable again. There's nothing logged to help indicate any issue.
Does anyone have any ideas as to what type of thing could cause this behavior or any ideas on how to trace the problem.
Thanks
May 9, 2012 at 11:14 pm
set the sql profiler and trace the lock and deadlock there.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 10, 2012 at 12:39 am
DMVs such as sys.dm_exec_requests, sys.dm_tran_locks are the right tools for tracing blocking issues.
But how will you fix it? Better to report this to your ERP and CRM application vendors.
May 10, 2012 at 6:29 am
Thanks for the suggestions, I was hoping to avoid profiler since it can be a couple of weeks between events, so the DMV's might work. Are they less likely to be locked/timed out compared to sysprocesses? Problem is I don't have the luxury of downtime, so have to remedy the situation pretty quickly. Arming myself with something that will find the culprit is really the key.
As for who has to fix it, there's quite a few venders involved with various bespoke java and .net hooking into the apps, all parties claim its not their code so need to prove who's fault it is first.
May 10, 2012 at 10:21 am
sysprocess is the old method. DMV is the better and recommended method.
Using DMVs it is possible detect blocking sessions with the application name, user name, host name, IP, SQL Command, etc.
Create an SQL Agent job to run every two minutes or so.
If there there is any blocking for more than x minutes, job should send you email/message.
Once you get the email/message, you can look into it and take required action.
This is the usual way of handling blocking issue.
May 11, 2012 at 7:14 am
Here is what I use to find blocking:
http://qa.sqlservercentral.com/scripts/Head+Blocker/75366/
In the past month, had two occasions to use it. Worked perfectly. Someone was running a report in the middle of the day, so it apparently was causing a table lock, so inserts couldn't occur for receipting.
One thing to do is note exactly what the blocker and the blockee are doing at the time, note the date, time, day of week, day of month, what they are each running, etc.
May begin to notice a pattern after a few of them. The above script gives login, database, application, who is blocking, who is being blocked, their host name, and even IP address. Very handy.
Just a suggestion, since you are seeming to not be able to directly identify the issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply