CODEWITHSUNDEEP
sqlsql-server-2008-r2ssms
user1345260
user1345260

Reputation: 2249

SQL Server Query doesn't finish execution

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

Upvotes: 5

Views: 13769

Answers (1)

tjibbe chris
tjibbe chris

Reputation: 160

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!)

Upvotes: 5

Related Questions