April 29, 2012 at 6:15 am
I have a problem with client who gets SQL time out error in our application.
I don't have access to their server and only data I could get is in form of screenshot from their DBA that a process is stuck.
The issue is a very simple insert query (int and strings) getting IX lock and doesn't allow other query to run. Basically this is all the info I'm getting from client without the ability to see more.
So I took their backup (after the problem occurred) to my server and restored. The same function didn't receive a lock. We did the same on client's VM and fresh SQL install. Same thing.
The difference of course is that at my side its SQL 2005 Standard and its not a busy server and their VM is SQL 2005 Express on XP machine.
While the production server is a big clustered server SQL 2005 Enterprise.
Now officially this isn't my problem anymore but I still would like to try to help and get some experience for the future.
So I'm thinking its some kind of lock configuration server side.
Unfortunately I don't have much experience in those issues so I thought if anyone else could give me advice what other possibilities are there?
I know it seems like there is little data to work with. I'm not expecting a solution, but maybe a direction what can be checked?
Thanks
May 1, 2012 at 6:30 am
Sounds like an open transaction. Possibly a bad piece of code that opened a transaction but then left it open. Or, maybe an error on the server that resulted in the same thing. It's hard to know remotely and you're right, just having a copy of the code isn't going to tell you much. You'll need to be able to access the server and look at the situation as it's occurring to understand what is blocked, what is blocking, and why.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2012 at 8:09 am
Thanks for the reply.
I did get an access remotely to the server and just saw the same query being stuck. It wasn't part of a transaction.
I also tried to replicated the conditions with a VM of same Windows Server version and same SQL version with same SP installed, alas I can't recreate the problem.
Also I have to stress its not a copy of the code. Its a full copy of their application including the data and SQL backup which was done after the problem started.
It just doesn't make sense to me that it works on another instance.
The only thing I can't recreate is the load on their production server.
edit: can = can't
Also maybe its related but few months ago the client changed (without our knowledge) the compatibility mode from 80 to 90. Of course right nobody can't even log in to the program since the "join querry" format was changed and it fails. We instructed them to put it back right away.
But now our development team blames any problem they have on this. Anyone can comment if this could cause a problem later?
From what I understand it changes the current T-SQL language the server tries to use against the database.
May 1, 2012 at 8:27 am
If you can access the server, what's the status of the "stuck" process? Is it waiting on something? Check sys.dm_exec_requests to see what it's doing. Also, everything is part of a transaction, even if it's just an implicit transaction, so check the transactions DMO too. You can combine them through the session_id. You can also combine this with sys.dm_exec_sql_text and use the offsets that will be available within sys.dm_exec_requests to understand exactly which statement is giving you trouble.
No SQL code is changed when you modify the compatibility level. So I wouldn't assume that's an issue with anything relating to code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2012 at 10:39 am
Thanks, I'll try to check these things out if I ever get a chance to connect again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply