?
Solved

how do I know which user is locking the table?

Posted on 2014-10-20
25
Medium Priority
?
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +2
25 Comments
 
LVL 7

Assisted Solution

by:slubek
slubek earned 500 total points
ID: 40393684
First, check open transactions:
dbcc opentran('DatabaseName')

Open in new window

Next, process details:
sp_who2 PID

Open in new window

0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 376 total points
ID: 40394262
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 38

Assisted Solution

by:PatHartman
PatHartman earned 504 total points
ID: 40394872
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 85

Assisted Solution

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

Assisted Solution

by:slubek
slubek earned 500 total points
ID: 40395316
Maybe using ACE drivers help?
0
 
LVL 38

Assisted Solution

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

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 620 total points
ID: 40395358
@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 500 total points
ID: 40395372
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 38

Expert Comment

by:PatHartman
ID: 40395420
Duh!!!  Ouch!
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40395581
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 4

Author Comment

by:bfuchs
ID: 40395778
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 4

Author Comment

by:bfuchs
ID: 40396119
Hi Experts,
Perhaps you know of a securely way of removing those locks without causing corruption to the database?
0
 
LVL 7

Assisted Solution

by:slubek
slubek earned 500 total points
ID: 40396274
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 620 total points
ID: 40396595
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 38

Accepted Solution

by:
PatHartman earned 504 total points
ID: 40397244
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 620 total points
ID: 40397584
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 4

Author Comment

by:bfuchs
ID: 40398161
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 620 total points
ID: 40398200
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 4

Author Comment

by:bfuchs
ID: 40400587
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 376 total points
ID: 40400597
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 38

Assisted Solution

by:PatHartman
PatHartman earned 504 total points
ID: 40400605
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 4

Author Comment

by:bfuchs
ID: 40400640
@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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 620 total points
ID: 40400687
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 4

Author Closing Comment

by:bfuchs
ID: 40400976
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 58
ID: 40401854
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

752 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