Post reply

Table is not accessible in database

  • 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

  • June 7, 2010 at 2:00 pm

    #1177221

    Reboot

  • June 7, 2010 at 2:17 pm

    #1177223

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

  • June 7, 2010 at 2:21 pm

    #1177224

    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

  • June 7, 2010 at 5:13 pm

    #1177262

    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)

  • June 7, 2010 at 5:16 pm

    #1177263

    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)

  • February 13, 2013 at 1:57 am

    #1587320

    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

  • February 13, 2013 at 6:57 am

    #1587435

    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)

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

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