locked
Unable to access Database anymore RRS feed

  • Question

  •  

    Hi,

     

    I'm unsure what happened, but I'm using SQL Server 05 Developer Edition.  I've been working with a local database where I tried to add a clustered index on a huge table (about 50M records).  Well, I had to stop the query since it was maxing out my work computer.  Since then, I've been unable to open the database or run queries against it.

     

    When I open SQL Server Management Studio the database says In Recovery.  Once it's done, when I try to expand tables, I eventually get this error:

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

    If I run a Select * from INFORMATION_SCHEMA.TABLES query, it will go on for a while

     

    If I try to access the properties of the Database (right clicking), I get this error:

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Cannot show requested dialog.

    ------------------------------
    ADDITIONAL INFORMATION:

    Cannot show requested dialog. (SqlMgmt)

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    I'm unsure where to go from this point forward.
    Wednesday, November 14, 2007 7:47 PM

Answers

  • I know this is an old thread, but when I googled it was one of the very first to show up. So I will post the solution I got to this error.

    1. connect to the MASTER database of the server with the offending database
    2. run the query below to find what transactions are open
    3. kill any transaction that is blocking the SYSTEM SPIDS (1 to 50)

    select * from master..sysprocesses where blocked <> 0
    go
    sp_who2
    go
    -- find the blocked SPIDS, then check for open trans below, any value other than 0 means an open tran
    SELECT distinct(open_tran) FROM master..SYSPROCESSES WHERE SPID=<suspect SPID>
    -- now kill it!
    kill <suspect SPID>

    The database will go back to normal as soon as the kill ends. No service to restart, no boot required.

    Read
    http://blog.sqlauthority.com/2007/04/25/sql-server-alternate-fix-error-1222-lock-request-time-out-period-exceeded/ and http://msdn.microsoft.com/en-us/library/aa337412.aspx for more info.

    • Proposed as answer by MauricioRPP Friday, July 3, 2009 1:03 PM
    • Marked as answer by Maggie Luo Tuesday, October 16, 2012 8:39 AM
    Friday, July 3, 2009 1:01 PM

All replies

  • Pls confirm if you have restarted the Sql services ? if yes then pls check the error log to see how much % the database has recovered ! if not pls post the details from error log

    - Deepak

    Wednesday, November 14, 2007 11:45 PM
  •  

    Yes, it is 100% recovered, when I restart the service, it goes back into recovery mode.
    Thursday, November 15, 2007 12:16 AM
  • if it has recovered then you should be able to access it Smile pls confirm

    Thursday, November 15, 2007 12:29 AM
  • When I open SQL Server Management Studio the database says In Recovery.  Once it's done, when I try to expand tables, I eventually get this error:

     

    I've already posted this as my problem.  I've already tried the easy things.  I've also rebooted.

    Thursday, November 15, 2007 12:32 AM
  • I know this is an old thread, but when I googled it was one of the very first to show up. So I will post the solution I got to this error.

    1. connect to the MASTER database of the server with the offending database
    2. run the query below to find what transactions are open
    3. kill any transaction that is blocking the SYSTEM SPIDS (1 to 50)

    select * from master..sysprocesses where blocked <> 0
    go
    sp_who2
    go
    -- find the blocked SPIDS, then check for open trans below, any value other than 0 means an open tran
    SELECT distinct(open_tran) FROM master..SYSPROCESSES WHERE SPID=<suspect SPID>
    -- now kill it!
    kill <suspect SPID>

    The database will go back to normal as soon as the kill ends. No service to restart, no boot required.

    Read
    http://blog.sqlauthority.com/2007/04/25/sql-server-alternate-fix-error-1222-lock-request-time-out-period-exceeded/ and http://msdn.microsoft.com/en-us/library/aa337412.aspx for more info.

    • Proposed as answer by MauricioRPP Friday, July 3, 2009 1:03 PM
    • Marked as answer by Maggie Luo Tuesday, October 16, 2012 8:39 AM
    Friday, July 3, 2009 1:01 PM
  • Have you tried to do an
    DBCC CHECKALLOC(databasename)
    GO

    See: http://msdn.microsoft.com/en-us/library/ms188422.aspx

    For more SQL information see also http://www.g-productions.nl/mssql_functions.php
    Saturday, July 4, 2009 8:53 AM
  • I know this is an old thread, but when I googled it was one of the very first to show up. So I will post the solution I got to this error.

    1. connect to the MASTER database of the server with the offending database
    2. run the query below to find what transactions are open
    3. kill any transaction that is blocking the SYSTEM SPIDS (1 to 50)

    select * from master..sysprocesses where blocked <> 0
    go
    sp_who2
    go
    -- find the blocked SPIDS, then check for open trans below, any value other than 0 means an open tran
    SELECT distinct(open_tran) FROM master..SYSPROCESSES WHERE SPID=<suspect SPID>
    -- now kill it!
    kill <suspect SPID>

    The database will go back to normal as soon as the kill ends. No service to restart, no boot required.

    Read
    http://blog.sqlauthority.com/2007/04/25/sql-server-alternate-fix-error-1222-lock-request-time-out-period-exceeded/ and http://msdn.microsoft.com/en-us/library/aa337412.aspx for more info.

    This solution totally fixed my problem! Am definitely saving this...

    Friday, June 10, 2011 7:33 PM
  • Hi MauricioRPP,

    Your solution fixed my issue, Thanks a lot for your contribution! :-)


    Développeur Applications Informatiques Computer Applications Developer


    • Edited by InfoDemers Tuesday, July 3, 2012 1:54 PM
    Tuesday, July 3, 2012 1:54 PM
  • Hi Christine and InfoDemers, I'm glad to know that my answer helped you both, even with over a year between each post!

    I did not know back then, but after some more time working as a DBA I got to know that any process that blocks a system SPID (1 to 50) may generate the strange messages of SSMS when we try to access some database and get locked.

    If anyone gets the error messages pointed in the very first post, go check if your SSMS session is not locked by another one.

    cya,
    Wednesday, July 25, 2012 3:43 AM
  • Thanks all

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, April 15, 2013 4:59 AM