how do I know which user is locking the table?

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?
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slubekCommented:
First, check open transactions:
dbcc opentran('DatabaseName')

Open in new window

Next, process details:
sp_who2 PID

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
PatHartmanCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Rats .... no, I don't know of anything with ACE.
0
slubekCommented:
Maybe using ACE drivers help?
0
PatHartmanCommented:
slubek,
I believe that is only relevant when you are accessing an ACE database from a non-Access application such as VB.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@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
slubekCommented:
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
PatHartmanCommented:
Duh!!!  Ouch!
0
bfuchsAuthor Commented:
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
bfuchsAuthor Commented:
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
bfuchsAuthor Commented:
Hi Experts,
Perhaps you know of a securely way of removing those locks without causing corruption to the database?
0
slubekCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
bfuchsAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
bfuchsAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
PatHartmanCommented:
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
bfuchsAuthor Commented:
@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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
bfuchsAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.