Table is not accessible in database

Question

Post reply

Table is not accessible in database

sqldba4u

Hall of Fame

Points: 3414
More actions
June 7, 2010 at 1:44 pm

#233461

A table in sql server database is seems to be locked and is not accessible.

I am executing Select statement, it keeps on running.

How to resolve this issue?

Thanks

Vivek

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

danderson 57106 Valued Member Points: 62 More actions · Answer 1

danderson 57106

Valued Member

Points: 62

June 7, 2010 at 2:00 pm

#1177221

Reboot

sqldba4u Hall of Fame Points: 3414 More actions · Answer 2

sqldba4u

Hall of Fame

Points: 3414

June 7, 2010 at 2:17 pm

#1177223

No, I can't do that since it is Production Server.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

This has to be the worst advice ever... Can't believe someone actually posted that...

Here's what I'd do:

Run sp_who2, once your command is blocked, so you know what blocks you (Column blkby).

That will tell you the SPID of the command that is blocking the table, then you can investigate what is this SPID currently running, and see the SQL that is being executed. There must be a long running process, or a transaction that is still opened on a query editor, or something else, you must identify the blocking, then post your result.

Cheers,

J-F

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

danderson 57106 (6/7/2010)

Reboot

Lordy, I hope you're not serious.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Vivek29 (6/7/2010)

A table in sql server database is seems to be locked and is not accessible.

I am executing Select statement, it keeps on running.

How to resolve this issue?

Thanks

Vivek

I agree with J-F... you need to find out what the cause is and J-F's post contains the first line of attack on correctly identifying the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

lukaszpoczta SSC Rookie Points: 32 More actions · Answer 6

I strongly recommend that you avoid killing SPIDs if at all possible. Killing a SPID can actually leave it in a permanent 0% rollback that consumes a whole lot of CPU time doing nothing. The only way to kill those is to bounce the service or the server. It's better to, if you can, find the person with the SPID you want to kill and have them close their session. I realize that's not always possible, especially with external users, but it's worth the try.

--Jeff Moden

Jeff Moden has absolutely right.

In the worst case

you can also use

select * from sys.dm_exec_sessions

to find session which is hanging and kill them manualy

or try to use below store procedure to kill all opened database session



CREATE PROCEDURE [dbo].[KillOpenedDBSessions]

	@DBName nvarchar(255)

AS

BEGIN

	SET NOCOUNT ON;

DECLARE @DatabaseName nvarchar(50)

DECLARE @SPId int

DECLARE @SQL nvarchar(100)

SET @DatabaseName = @DBName

DECLARE my_cursor CURSOR FAST_FORWARD FOR

SELECT SPId FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @SQL = 'KILL ' + CAST(@SPId as nvarchar(10))

 print @SQL

 EXEC sp_executeSQL @SQL

 --KILL @SPId -- Causing Incorrect syntax near '@spid'.

 FETCH NEXT FROM my_cursor INTO @SPId

END

CLOSE my_cursor

DEALLOCATE my_cursor

SELECT SPID,status,hostname,program_name,loginame,login_time FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName)

--AND SPId <> @@SPId

SELECT @@Spid as my sesion

END

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

I strongly recommend that you avoid killing SPIDs if at all possible. Killing a SPID can actually leave it in a permanent 0% rollback that consumes a whole lot of CPU time doing nothing. The only way to kill those is to bounce the service or the server. It's better to, if you can, find the person with the SPID you want to kill and have them close their session. I realize that's not always possible, especially with external users, but it's worth the try.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions