5

I apologize for the nOOb question but I have a very simple SQL Server Query which SELECTS data from a table. The table is about 18 rows big in size and the query is as below,

SELECT * FROM SavedJobs

And this never finishes execution on SQL SERVER MANAGEMENT STUDIO

but when run using WITH(NOLOCK) it spits out the result, for example,

SELECT * FROM SavedJobs WITH(NOLOCK)

I believe something on my database has locked the SavedJobs table and I would like to figure out and kill that lock. Can someone please guide me in solving this issue?

Output from sp_lock:

spid        dbid       ObjId     IndId     Type      Resource             Mode    Status
51           4              0              0              DB                                                          S              GRANT
52           7              0              0              DB                                                          S              GRANT
53           7              0              0              DB                                                          S              GRANT
54           4              0              0              DB                                                          S              GRANT
55           7              0              0              DB                                                          S              GRANT
56           7              0              0              DB                                                          S              GRANT
57           7              0              0              DB                                                          S              GRANT
58           7              0              0              DB                                                          S              GRANT
58           1              1131151075         0              TAB                                                        IS            GRANT
62           4              0              0              DB                                                          S              GRANT
63           4              0              0              DB                                                          S              GRANT
64           7              0              0              DB                                                          S              GRANT
66           9              0              0              DB                                                          S              GRANT
67           9              0              0              DB                                                          S              GRANT
68           7              0              0              DB                                                          S              GRANT
68           7              608721221           1              KEY         (ff6a151f422b)                                  X             GRANT
68           7              608721221           1              PAG       1:246                                     IX            GRANT
68           7              608721221           2              PAG       1:250                                     IX            GRANT
68           7              608721221           0              TAB                                                        IX            GRANT
68           7              32719169             0              TAB                                                        IX            GRANT
68           7              608721221           2              KEY         (937ccdaf17f5)                                  X             GRANT
69           7              0              0              DB                                                          S              GRANT
70           7              0              0              DB                                                          S              GRANT
71           9              0              0              DB                                                          S              GRANT
72           7              0              0              DB                                                          S              GRANT
74           9              0              0              DB                                                          S              GRANT
75           7              0              0              DB                                                          S              GRANT
76           9              0              0              DB                                                          S              GRANT
78           9              0              0              DB                                                          S              GRANT
79           9              0              0              DB                                                          S              GRANT
85           9              0              0              DB                                                          S              GRANT
86           7              0              0              DB                                                          S              GRANT
89           7              608721221           1              PAG       1:246                                     IS            GRANT
89           7              0              0              DB                                                          S              GRANT
89           7              1243151474         0              TAB                                                        IS            GRANT
89           7              699149536           0              TAB                                                        IS            GRANT
89           7              768721791           0              TAB                                                        IS            GRANT
89           7              608721221           0              TAB                                                        IS            GRANT
89           7              608721221           1              KEY         (ff6a151f422b)                                  S              WAIT
89           7              32719169             0              TAB                                                        IS            GRANT
96           9              0              0              DB                                                          S              GRANT
98           7              0              0              DB                                                          S              GRANT
100         7              0              0              DB                                                          S              GRANT
101         9              0              0              DB                                                          S              GRANT
102         9              0              0              DB                                                          S              GRANT
103         9              0              0              DB                                                          S              GRANT
105         7              0              0              DB                                                          S              GRANT
106         7              0              0              DB                                                          S              GRANT
Improve this question
5
  • 2
    Execute sp_lock and show us the output. Commented Oct 7, 2013 at 14:22
  • Barry - I updated my question with the output Commented Oct 7, 2013 at 14:35
  • 3
    OK, execute DBCC INPUTBUFFER (68) and show us the contents of the EventInfo column. That's the command that session 68 is executing that is blocking your command. Commented Oct 7, 2013 at 14:42
  • Hi Barry, EventInfo: delete from JobMaster Your help would be highly appreciated. Commented Oct 7, 2013 at 14:48
  • 2
    I ran the command KILL 68 and it worked. Thanks a lot for being patient and working with me to resolve the issue. Commented Oct 7, 2013 at 15:01

1 Answer 1

Reset to default
5

You can execute sp_who2 in another session to show which session is blocking another one. You can use kill to kill a process (not only the lock!)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Chris -- Thanks for the answer. I ran sp_who2 and it shows bunch of results where people are accessing my database. How do I kill them and also before killing them how can I make sure they are accessing the same table as mine
"people are accessing my database. How do I kill them" better not at all ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.