?
Solved

a Process locked a table (SQL 2000)

Posted on 2010-08-24
7
Medium Priority
?
707 Views
Last Modified: 2012-08-13
I have a SQL Server 2000 database that when the users came in Monday morning a table was locked and my users couldn't use my application.  This application with the SQL backend database has been around for 9 years and we have never had this problem.  The user who locked the table (who was off Monday) was the new computer guy for the department and he was admittedly playing with the database so he could become familiar with it.  Note the computer guy is not a SQL Server person and was not writing procs but might have been doing queries or playing with the data in the table.

My questions
* I don’t understand how “a process” could lock a table.  By locking a table (my terminology) the particular table if opened in Enterprise Manager would time out and not show any records.  What I’m looking for is a couple of  “SIMPLE” examples of how to lock a table so it would time out.

* I had to go through the DBA who I was lucky enough she wanted to work with me.  Once we determined the problem she “Stopped the process” and all was fine.  How do I see a locking process in Enterprise Manager 2000 and then stop it.  (I don’t have the rights to Enterprise Manager on the live db but I would like to see it on my local copy  [I manage the live db through an Access adp])

A little more info:
I'm a programmer that uses SQL Server but my expertise is with the front end not the backend.
0
Comment
Question by:LJG
[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
  • 3
  • 3
7 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33511691
>> Note the computer guy is not a SQL Server person and was not writing procs but might have been doing queries or playing with the data in the table.

Did he opened up any Transaction using those tables and have closed it properly.
If he didn't closed it, then that might cause the table to get locked till the transaction is closed out.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33511766
use WITH (NOLOCK) when you trying to fetch record from a table (SELECT Query from table)
0
 
LVL 2

Author Comment

by:LJG
ID: 33511858
rrjegan17 my problem is that I'm not sure what he did, but I'm sure he didn't write any procedures that used transaction, but he might of linked to the table with Access.  Is it possible he added a record maybe, then did a delete query and didn't complete it (Access would ask are you sure you want to delete).  He got interrupted and never answered OK?

Would this have locked the table from Friday to Monday?

Thanks for your help.
LJG
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 33521732
>> Is it possible he added a record maybe, then did a delete query and didn't complete it (Access would ask are you sure you want to delete).  He got interrupted and never answered OK?

Yes, it can definitely.
what I meant above was similar ( Modifying records within a transaction and not committed it).

Can you check whether any active transactions are running in the server using the below query:

SELECT *
FROM sys.dm_tran_active_transactions
0
 
LVL 2

Author Comment

by:LJG
ID: 33533336
If a table is locked in SQL 2000 - Here are some thoughts.

0) Create the below view :
    SELECT     master.dbo.sysprocesses.spid, master.dbo.sysdatabases.name AS db_name, master.dbo.sysprocesses.hostname AS MachineName,
                          master.dbo.sysprocesses.nt_domain, master.dbo.sysprocesses.nt_username, master.dbo.sysprocesses.cmd, master.dbo.sysprocesses.blocked,
                          master.dbo.sysprocesses.login_time, master.dbo.sysprocesses.last_batch, master.dbo.sysprocesses.status, master.dbo.sysprocesses.dbid,
                          master.dbo.sysprocesses.open_tran, master.dbo.sysprocesses.program_name, master.dbo.sysprocesses.hostprocess
    FROM         master.dbo.sysprocesses INNER JOIN
                          master.dbo.sysdatabases ON master.dbo.sysprocesses.dbid = master.dbo.sysdatabases.dbid
    WHERE     (master.dbo.sysprocesses.dbid = 10) AND (master.dbo.sysprocesses.open_tran > 0)



1) Run the above view and get the SPID that is causing the problem.

2) Run --> sp_Lock  with the SPID  from above on the end --> eg -->  sp_Lock 54
     Get the ObjId

3) Run SELECT object_name(with the above ObjId)  eg -->
    Use Your Database Name
    SELECT object_name(1298871744)

4) You now know Who, Where, and What Object is Locked.

5) Kill the Process  -->   Kill with SPID  on the end eg --> KILL 54

6) You are good to go

7)  You might also want to look at --> DBCC OPENTRAN WITH TABLERESULTS


Hope this helps someone.
LJG
0
 
LVL 2

Author Closing Comment

by:LJG
ID: 33533371
rrjegan17 -
You gave me the answer to what was locking the database - was able to confirm that truely lockes the table.

Also a great place to start on finding and unlocking such locks even though

SELECT *
FROM sys.dm_tran_active_transactions

does not work in SQL 2000 I was able to find some other solutions.  If someone is interested to my post below.

Thanks again
LJG
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33542769
>> does not work in SQL 2000 I was able to find some other solutions.  If someone is interested to my post below

In SQL Server 2000, approach mentioned in your comment would hold good with few modifications.(simplifying the query)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 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