I canceled a query which took too long time. After that, the SQL-Server database was locked. After I restarted SQL Server, the mode of my database was "in recovery". Now I can't access my database anymore. If I look at the properties, I get the following error message (I am domain Admin):

Property Size is not available for Database X. This property my not exist for this object, or may not be retrievable due to insufficient access rights.

If I access the views, I will get following Message:

Lock request time out period exceeded

SQL Server Version: SQLServer 2008 R2

Can anyone help me please? This database should be moved to production tomorrow.

  • There are some suggestions in the answers to this question. – criticalfix Apr 10 '13 at 17:55
  • Or maybe here. – criticalfix Apr 10 '13 at 17:59
  • What does this query return? select state_desc from sys.databases where name = 'Your Database Name'; Where 'Your Database Name' is your database name. – Thomas Stringer Apr 10 '13 at 18:00
  • What was the query? An ordinary DML, or something administrative? – Jon of All Trades Apr 10 '13 at 18:07

If you look in the ERRORLOG file you'll probably see that the database is in the process of rolling commands forward or backward. Once that process is done the database will come back online. All you can do at this point is wait.

DO NOT restart the SQL Server instance again. All you'll do is cause the SQL Server to start this process over again.

However long the query ran for, you can expect the crash recovery process (that's what is running at the moment) to take about that long to complete.

| improve this answer | |
  • Hi denny, thanks for your answer. You are right, the recovery process is finished right now. Didn´t know that it can take so long. Thanks – Alex Apr 10 '13 at 18:19
  • 1
    Glad it's all finished. Recovery can take a very long time to run. It all depends on how many transactions there are to roll forward and backward and how large those transactions are. I've managed systems before that if the app servers weren't shutdown first, the database could take hours to come back online. Explaining that to management was lots of fun. – mrdenny Apr 10 '13 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.