Solved

how do I know which user is locking the table?

Posted on 2014-10-20
25
172 Views
Last Modified: 2014-10-24
Hi Experts,
I am trying to add a field to a SQL 2008 table using SSMS, but its telling that the table is in use... How do I know which user(s) are keeping a lock and holding back?
Also in an access database, is there a way to check which user is preventing from opening a table in design mode?
0
Comment
Question by:bfuchs
  • 7
  • 6
  • 5
  • +2
25 Comments
 
LVL 7

Assisted Solution

by:slubek
slubek earned 125 total points
Comment Utility
First, check open transactions:
dbcc opentran('DatabaseName')

Open in new window

Next, process details:
sp_who2 PID

Open in new window

0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 94 total points
Comment Utility
Also in an access database, is there a way to check which user is preventing from opening a table in design mode
You can check the .ldb or .ldaccdb locking file associated, but that often doesn't give you the necessary information.

Some have used the JetRoster method:

http://www.fmsinc.com/MicrosoftAccess/monitor/user-roster.htm

That page shows the VBA code needed to do this. Your database would need to have a Reference to the ADO library in order to use this. I'm not entirely sure this would work with 2007 or higher, but it should, assuming the correct ADO files are available on the machine.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 126 total points
Comment Utility
Scott,
I just tried to run this against an .accdb  (I added the ado 2.8 library reference to get rid of the compile error) and get a runtime error of -2147467259(80004005) - unrecognized database format.
Do you know of anything that will work with ACE?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 94 total points
Comment Utility
Rats .... no, I don't know of anything with ACE.
0
 
LVL 7

Assisted Solution

by:slubek
slubek earned 125 total points
Comment Utility
Maybe using ACE drivers help?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 126 total points
Comment Utility
slubek,
I believe that is only relevant when you are accessing an ACE database from a non-Access application such as VB.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 155 total points
Comment Utility
@Pat,

Change:

    .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb"

 to

    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Northwind.mdb"

Jim.
0
 
LVL 7

Assisted Solution

by:slubek
slubek earned 125 total points
Comment Utility
PatHartman,

Change Provider to "Provider=Microsoft.ACE.OLEDB.12.0" in connection string in example code from User Roster tip.

As for question #2 - we don't know which Office version bfuchs has.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Duh!!!  Ouch!
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Experts,

Thanks all for replying,

@slubek,
dbcc opentran('entered my DatabaseName') does not return anything, while I know there are currently many users connected.
sp_who2 gives me all users connected (similar to activity monitor), but what I need is only those posing a lock on a specific table at the current time.

FYI- Office version of MDB is 2000, while some users have 2003.

@Scott,
I will test that & let you know.

Thanks
Ben
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Scott,

I tested & that also gives me all users connected to the database, but the story is as follows, I have a large app that entails many forms each bound to one or more tables, and when I am trying to do a change to an object, It currently require me to have all users logging off (I know who the users are in sql from activity monitor and in access from the servers file manager), So what I am really looking for is a way to know which are the users locking a particular object.

Thanks,
Ben
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Experts,
Perhaps you know of a securely way of removing those locks without causing corruption to the database?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Assisted Solution

by:slubek
slubek earned 125 total points
Comment Utility
Perhaps you know of a securely way of removing those locks without causing corruption to the database?
If you are asking about SQL Server, you can kill chosen PID with
KILL PID

Open in new window

0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 155 total points
Comment Utility
Ben,

  Within an Access DB, there is no way to determine who is accessing specific objects or tables unless you build that in yourself.

 Locks are placed on the various objects and there used to be a view DB lock utility, but the locking scheme has been changed several times since it was released, so it no longer works.  Also the current locking scheme is undocumented, so even trying to write a utility to do that is next to impossible.

 The only thing you'll be able to detect easily is if someone is connected to the DB or not with the user roster.

If you want to track within the DB, have a look at this:

http://www.experts-exchange.com/Database/MS_Access/A_5328-Resource-locking-in-your-applications.html

 as one possible approach.

Jim.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 126 total points
Comment Utility
Knowing which user is locking a specific object is not necessary.  You should NEVER, EVER make design changes to a database while there are active users accessing it.  In A2007 and newer, many changes are simply not allowed at all if there are any other users.  For most design level changes, you need exclusive control of the databases.  In any event, all changes should be made to your development copy of the FE and then applied by having the users shut down and reopen the app to copy the new version to their personal workspace.  Changes to the BE are more difficult.  Again, all users must be out completely.  I always schedule BE changes for evenings or weekends to avoid disrupting daily activity.

Knowing which user has the database open, gives you enough information to contact them and ask them to log out.  There are also methods that revolve adding a timer to a hidden form that will allow you to track usage and shut down inactive sessions or Force close based on setting a value in a record in a table that the timer will periodically check.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 155 total points
Comment Utility
FYI here's the inactivity timer Pat was referring to:

How to detect User Idle Time or Inactivity in Access 2000
http://support.microsoft.com/kb/210297

Jim.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Experts Thanks all for replying,

@slubek,
I use SSMS activity monitor to kill process in SQL, with this killing issue I was mainly concern for our Access database that is more prone for corruption.

FYI- Our data is split half in SQL and the other half is in an Access database.

@Jim,
there used to be a view DB lock utility, but the locking scheme has been changed several times since it was released, so it no longer works
Since we are still using Access 2k, very possible it will work, can you post a link to that utility?
I will test your link posted above & keep you posted.

@Pat,
The situation here is as follows, during the day its not possible to have all users close...period.
Therefore when I am about to work on the database, I ask all users to close before they leave, and I am working at night hours remotely, if someone didn't got the message or didn't listen..I am left with only one option, to force a close from the server and take a chance of corruption...this is why I am looking for a way to find out the particular user that is posing the lock on that table I need to work with (on the back end only).

Re the suggestion of using a timer event, the manager don't like that idea, he's afraid of slowing the whole day's process..
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 155 total points
Comment Utility
It's back from the JET 2.x days....don't even think it was updated for JET 3.x, and I know for sure not for JET4.x

But I've got a copy of it here some where...

Jim.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Jim,

From what I see that solution (ID: 40396595) involves saving in a table every time a user access something, its not like the roster utility that just running that function...So that might impact the system as well, the way the timer does.

what I really don't understand is, since in Access its much more important to know who are the users then in sql (due to the corruption issue, in my eyes), why don't they have something like this tools, and if they had already like you're mentioning above dblock, why doesn't it get upgraded?

One more thing, I once read an article about something called DBfreelock, wonder if someone knows about it, this is basically what I am looking, a utility that would release safely all locks of the database without the risk of corruption.

@slubek,
Since in activity monitor you can see each user what statement are they running, I would assume there is a function/store procedure that could extract only those users who have a particular table as part of the sql statement.

Thanks,
Ben
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 94 total points
Comment Utility
why don't they have something like this tools, and if they had already like you're mentioning above dblock, why doesn't it get upgraded?
MSFT never really considered Access to be an enterprise level sort of tool, and therefore never really took the time to implement enterprise-level tools to work with it. Essentially, if you want this sort of control, you're expected to move up to SQL Server, Microsoft Server, etc etc. The Object Model of Access doesn't lend itself well to that sort of control, and there just was no reason to take it in that direction, since MSFT already had products that could do that. Sort of like asking why Ford doesn't put the 7.3l diesel motor in the Ford Focus ... makes no sense to do so.

The timer method suggested above is probably the simplest way to do this, and is not something that would impact performance, at least in my experience (I've implemented exactly that scenario in quite a few Access apps). It'll do what you want, and has a faaaaar less chance of causing corruption than anything you could do that would force a shutdown to clear locks - so I'm not sure why the "manager" is reluctant to do something like this. It's quite common with those who have no idea what they're doing (not you, the manager) to insist on doing something (or not doing it) just because it doesn't "feel" like it would work. Pure BS, in my opinion, for non-technical people to get involved in these sorts of decisions, and nothing but a huge monkey wrench in the works.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 126 total points
Comment Utility
Ben,
I'm pretty sure that somewhere in the lock file, Access really does know what user has what locks against what objects.  It is just that the lock file is completely undocumented and no one has tried to extract this low level information from it.

People swear by the timer event to solve this problem.  Personally, I don't like using it.  You have to be extremely careful when you are in testing mode because if a timer is running and you put something into design view, it is likely to corrupt.  In the one app where I used timers.  I had the code turn them all off when I logged in to the database under my own ID just to prevent that from happening.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
@Scott,
Your last comment is worth cash..if I could only show that to the menager-:)

I just wondering, since our new server displays all users who had that mdb and ldb file open, along with their IP address, is there a way I can connect to their pc and close it myself? (already tried with remote desktop but didn't work, looks like there is a fire wall..) maybe someone has a work around to this?
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 155 total points
Comment Utility
Dbfreelock is not what you think.  It was designed to release orphaned locks in the OS after a user terminated their connection to the DB.   It is not meant to disconnect active users or tell you who is accessing specific objects. This allows you to clear a lock without having to reboot the machine you actually can do with the program does manually as well.  Jet also has a passive shutdown feature but not many use it when you activate it it's it's a flag that does not allow anyone else to connect to the DB but it will not force existing users out.

As for the locking scheme it has been documented for jet 2.0 and 3.0.  jet uses what is called extended byte range address locking.  
Locks are placed against the LDB file on parts of the file that don't exist. These locks are held by the OS and are never physically written to disk. The LDB file will never grow more than 16 K it holds an array of 64 byte entries. 32 biytes for the machine name and 32 bytes for the username and there are 255 slots one for each user.

Jim
0
 
LVL 3

Author Closing Comment

by:bfuchs
Comment Utility
Hi Experts,
At this point if the top Access experts cant find another solution I guess nobody else will...So its time to finalize this thread.
Thanks very much for all your input, I really enjoyed reading them,
Ben
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Just to add a couple of points:

1. DBFreelock, which was designed to be used in conjunction with JET/ACE DB's could actually be used with any file.   Process explorer from Sysinternals can also kill off orphaned locks for any file.  File locks are used with every file, JET/ACE just uses a lot of them in a unique way to lock pages and records.

2. I cannot find the view DB lock utility here anywhere.   It's a moot point however because while it was updated for JET 3.5, it was never updated for JET 4.0 or ACE and record level locking (and the method used for adding that has never been documented).   Not sure the locking scheme was changed for ACE, but I know it certainly was for JET 4.0 because of record level locking, so last supported version for the utility would have been Access 97.

3.  For the sake of some Access trivia, here's what it looked like:

screen shot view DB Lock
  I remember it was quite tedious to use since it worked from the page locks themselves and then told you what object was involved (another reason I didn't think it would be all that helpful).

Jim.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now