October 27, 2008 at 9:35 am
Is there a way to trace down locks held by users that have been deleted?
I have users trying to run queries, their program is telling them that a user has some rows locked, but the user hasn't been in the system for months. Is this even possible? This server has been rebooted since this user has left the company.
The system Admin has told me that he does not see the old user in the application/admin side as well.
October 27, 2008 at 9:48 am
todd_dawson (10/27/2008)
I have users trying to run queries, their program is telling them that a user has some rows locked, but the user hasn't been in the system for months. Is this even possible?
Maybe. How is the program tracing the locks and owners?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 9:59 am
It could be that a SQL Agent job is being run by the user account still, it may just his windows account has been removed. Check if that user account has been set up as a local user on the server and a job is set up to run in that user name.
Its always safest to set up your jobs to either run under the sa account, in which case they will run under the service account, or set up an account with appropriate permissions to run a job.
October 27, 2008 at 10:18 am
Gila, I have no idea how the program traces its users, I just wanted to cover all my bases on the SQL Server side before opening a help desk ticket with the vendor.
The only jobs that run on the server, under the agent, are maintenance jobs and they all have sa as their owner. Also, the log-in has not been added as a local user to the windows server.
October 27, 2008 at 12:01 pm
When a user disconnects, all SQL Server locks are released. You cannot have a resource locked without the associated spid. So, if you have users that are not actually connected to the database and the application is indicating they have records locked, the application must be using it's own locking mechanism rather than the internal SQL record locking.
This is one of the bigger problems with most "I can do it better" home-grown locking mechanisms. They tend to leave garbage uncollected when a session terminates incorrectly.
I would take this issue back to the vendor.
October 27, 2008 at 12:17 pm
At least find out from the vendor where that locked rows report is coming from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 1:18 pm
You are probably looking at an application locking scheme where a row is entered in a table to identify when a record/row is locked by a user.
You can find out what table is being checked by using profiler to capture the query being run that is returning the information about the 'locked' rows.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2008 at 8:44 am
I started a trace on the server, but I am unsure if I have it setup correctly.
Are there any good online tutorials that may help to point me in the right direction with SQL Profiler?
Is there anything that you would look at specifically? I dont think the execution of the query/command is coming from the deleted users log-in, would you try to capture everything executed by the user saying they are getting a lock, this is what I am attempting to do now.
What event categories should I be looking at?
Thanks for all the suggestions.
October 28, 2008 at 10:13 am
The RPC:completed and SQL:Batch completed are good events to use to see what's running against the server.
First thing is to capture whatever code the app is using to show the locks. Get the users to run the query that gives the 'locked rows' error and see what the app's doing and what it's calling.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2008 at 1:01 pm
Well I tried tracing the listed event categories, and got no results, so I traced every transaction on the server, if it could be checked marked I checked it. 🙂
I then did a search on the log and couldnt find his log-in anywhere, its like there was no transaction generated on the server. I was seeing alot of activity from other users, but nothing except his connection string showed in the log.
This also is not limited to one users log in, this is happening to anyone who tries to attempt a save, on this one item.
Keeps looking more and more like a vendor issue. Any more suggestions?
October 28, 2008 at 1:15 pm
If you still have the trace output available - search the text data for the word 'locked'. Basically, you need to identify the request that is being returned that is causing the users to receive the message that a row is locked.
You will not find a reference to this user that is no longer accessing the system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2008 at 1:43 pm
I was looking for all transactions executed by the user getting the error. Not the old user that still has the lock in the server. No luck either way, only took a couple of seconds anyway with a 'CTRL-F'.
I went back and searched for 'locked' as well without the quotes and got no results.
October 28, 2008 at 1:57 pm
Well, this is going to be a bit harder and most likely will end up going to the vendor anyways. What you need to do is isolate the trace down to that single user getting the problem. That way, you see everything that user is doing.
Once you are sure you are tracing just that users session - stop the trace, clear the results and have the user navigate in the application to a point just before they get the error.
Start the trace and have the user continue processing until the get the error. As soon as they get the error, stop the trace.
One of the calls that you captured will be the one where it is getting the error. Now, you can review each call, identify the object being used (view, table, stored procedure, function, etc...) and then review what each object is. If the object is a view, repeat the select statement and see what is returned, the same with a table. If the object is a stored procedure - review the procedure to see what tables\views are accessed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2008 at 3:39 pm
todd_dawson (10/28/2008)
Well I tried tracing the listed event categories, and got no results, so I traced every transaction on the server, if it could be checked marked I checked it. 🙂
Eeep! Please don't do that. Tracing every possible event through profiler is one of the fastest and most sure ways of killing a server completely. :sick:
The two events I gave you will catch every procedure of query batch that gets sent to the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2008 at 3:53 pm
The server, I knew at the time, only had at most 3 users on it doing stuff. And I had one of the end users on the phone.
I started the trace, had him execute the task, then stopped the trace. But, I will try to what Jeffery suggested tomorrow, and have the user start at the very beginning of the whole process and try to see if there are transactions sent before the 'submit' button is pushed.
I will again setup the trace to only look at RPC:Completed and SQL:BatchCompleted.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply