none
Selecting restored database crashes SQL Server Instance

    Question

  • Hi.

    The company I'm working for uses SQL Server with their ERP software. I've been working with a (now outdated) backup, doing some development. Last week I decided to get a "fresher" copy.

    When I restore this backup, I can't select it from the dropdown menu (Available databases). If I click it's name, my instance crashes and the database goes into recovery mode. I can wait and run a query like "select top 10 * from [BACKUP_DB].[dbo].[item] but if I write it char by char, when I get to .[dbo]., SSMS "hangs" and takes a really long time to recover. After that I can run the query...

    I've tried a new backup this morning and restoring it in a virtual machine with a freshly installed instance, but I have similar issues.

    I've run:

    SELECT DATABASEPROPERTYEX ('BACKUP_DB' , 'Status' )
    DatabaseStatus_DATABASEPROPERTYEX
    GO
    SELECT state_desc DatabaseStatus_sysDatabase
    FROM sys .databases
    WHERE name = 'BACKUP_DB'

    GO

    ...which can show "in recovery", but if I wait for that to end and run

    DBCC CHECKDB ('BACKUP_DB')

    I get no relevant messages.

    We've had an issue a couple of weeks back when the instance "locked up". I think the systems administrator had to reinstall the instance but there are still problems lingering about, probably....

    I'm afraid to even touch the production database.

    I would appreciate any help with this.


    • Edited by Karbono Tuesday, July 17, 2012 5:34 PM clearer title
    Tuesday, July 17, 2012 5:25 PM

Answers

  • You are using SQL Server Express edition, from your Error Logs.

    <quote>2012-07-19 09:44:50.27 Server      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
        Jun 17 2011 00:54:03
        Copyright (c) Microsoft Corporation
        Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)</quote>


    Also I found following messages in your error log ..

    2012-07-19 14:41:01.47 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 14:41:46.71 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 14:42:26.71 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 15:05:22.61 Server      Server resumed execution after being idle 139 seconds: user activity awakened the server. This is an informational message only. No user action is required.
    2012-07-19 15:05:28.67 spid52      Starting up database 'A2010_20120719'.
    2012-07-19 15:06:28.19 spid52      Starting up database 'A2010_20120719'.
    2012-07-19 15:06:54.15 spid55      Starting up database 'A2010_20120719'.
    2012-07-19 15:53:49.09 Server      Server resumed execution after being idle 1227 seconds: user activity awakened the server. This is an informational message only. No user action is required.
    2012-07-19 15:53:50.27 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 16:44:18.56 Server      Server resumed execution after being idle 1672 seconds: user activity awakened the server. This is an informational message only. No user action is required.

    Therefore this indicates that your database has to keep re-openning, indicating your AUTO_CLOSE property is turned on.  Or database is being shutdown.  Please read this article t get further understanding of SQL Server Express Edition and Idle Timeouts.

    http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

    Thanks.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    • Marked as answer by Karbono Friday, July 20, 2012 9:47 AM
    Thursday, July 19, 2012 6:46 PM

All replies

  • Company's server SQL version:

    Microsoft SQL Server Management Studio                        10.50.1600.1
    Microsoft Data Access Components (MDAC)                        6.1.7600.16385
    Microsoft MSXML                                               3.0 4.0 6.0
    Microsoft Internet Explorer                                   8.0.7600.16385
    Microsoft .NET Framework                                      2.0.50727.4961
    Operating System                                              6.1.7600

    My SQL version

    Microsoft SQL Server Management Studio                        10.50.2500.0
    Microsoft Data Access Components (MDAC)                        6.1.7601.17514
    Microsoft MSXML                        3.0 6.0
    Microsoft Internet Explorer                        9.0.8112.16421
    Microsoft .NET Framework                        2.0.50727.5456
    Operating System                        6.1.7601

    Virtual Machine

    Microsoft SQL Server Management Studio                        10.50.1600.1
    Microsoft Data Access Components (MDAC)                        6.1.7600.16385
    Microsoft MSXML                        3.0 6.0
    Microsoft Internet Explorer                        8.0.7600.16385
    Microsoft .NET Framework                        2.0.50727.4971
    Operating System                        6.1.7600

    Tuesday, July 17, 2012 5:33 PM
  • Sounds like a issue on database or the backup itself.  Can you verify the backup is good?  I see you did DBCC CheckDB and returned nothing from your development database.  How about your production database?

    Check to see if any SQL Server dumps were generated with *.mdmp extension.  Also check the SQL Server ERRORLOG to see if any dump information been recorded, in hanging like that a SQL thread might have had an exception.

    You are running RTM version of SQL Server 2008 R2, maybe you need to update your SQL Server also.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Tuesday, July 17, 2012 7:50 PM
  • If after DB comes up , you have got no error messages by runing DBCC CHECKDB ('BACKUP_DB') & things runing fine after that. Then no need to woory now.

    When SQL restore backup, it perform REDO & UNDO opearations. Database is being recovered. The recovering process is a transient state; the database
    will automatically become online if the recovery succeeds. If the recovery fails, the database will come in suspect.


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Wednesday, July 18, 2012 7:19 PM
  • The thing is, recovery eventually ends, but the problem persists. If I select the database to be in use, the instance crashes and the database goes into recovery mode when the instance comes back up.
     So things are not running fine after that....
    • Edited by Karbono Wednesday, July 18, 2012 7:34 PM
    Wednesday, July 18, 2012 7:29 PM
  • I've never dealt with sql server dumps or the error logs, but I'll investigate and get back to you. I'll also check on the sql server version when I get back to work. Thanks.
    Wednesday, July 18, 2012 7:33 PM
  • Run DBCC CHECKDB('BACKUP_DB') after bringing database into Emergency & Single user mode. See if any relavent error recived.


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Wednesday, July 18, 2012 7:35 PM
  • - I asked our sys. admin. to do the same to the production db. I'm guessing it returned no error as well or he would have told me.

    - Updated my new instance to SP1.

    - CHECKDB found 0 allocation errors and 0 consistency errors in database 'BACKUP_DB'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    - Selecting backup_db from the "Available Databases" list query crashed the instance again.

    - No information was added to the errorlog file or any other file.

    - no memory dump file was created.

    I'm getting another backup from the server, but I'm not very optimistic...

    Thursday, July 19, 2012 11:35 AM
  • Lets step back a bit, is it SQL Server Engine that is crashing?  Or is it the SQL Server Management Studio crashing?  From the sounds of your response, since there is no Dump File, nothing in Error Log? And the item that is crashing is on drop-down list in Available Database list.  It seems like an issue with SSMS no?  Do you get a .NET error back from SSMS?  Or it just dies?  If so look at the application event log to see any errors are recorded in there...

    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Thursday, July 19, 2012 3:40 PM
  • I would say it's the engine, since SSMS doesn't actually crash. It just hangs for a few seconds. When it becomes responsive again, it can't connect to the instance. I need to wait a few seconds.

    I've uploaded 2 videos. Hopefully they help.

    http://youtu.be/HhKvDMk6dts

    http://youtu.be/afyK2VNC_lk

    Thursday, July 19, 2012 4:42 PM
  • Alright that officially is the most weird scenario.  If I can figure out how to reproduce it, might make it a great scenario for my class :). I have never seen database go Recovery after an execution of a query....

    There is no external factors we need to consider correct?  No jobs or other actions happening against the database right?  In the video it was just you accessing the database correct? 

    Even though there is no dump information in error log, is there anything in ERRORLOG file?  Can you please paste the contents of the ERRORLOG file here?  If they are large feel free to zip them up and mail them to me at mohitkgupta at msn dot com :).

    ERRORLOG file by default is in C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCENAME\MSSQL\Log\.  There should be ERRORLOG, ERRORLOG.1 through ERRORLOG.6.

    Thanks.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Thursday, July 19, 2012 5:23 PM
  • I'm the only one accessing it, and it had just been restored.

    I sent you the logs. You will notice a few warnings from when I tried to access it right after upgrading the instance to SP1. Apparently it's a known issue with that process.


    • Edited by Karbono Thursday, July 19, 2012 5:51 PM typo
    Thursday, July 19, 2012 5:33 PM
  • You are using SQL Server Express edition, from your Error Logs.

    <quote>2012-07-19 09:44:50.27 Server      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
        Jun 17 2011 00:54:03
        Copyright (c) Microsoft Corporation
        Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)</quote>


    Also I found following messages in your error log ..

    2012-07-19 14:41:01.47 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 14:41:46.71 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 14:42:26.71 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 15:05:22.61 Server      Server resumed execution after being idle 139 seconds: user activity awakened the server. This is an informational message only. No user action is required.
    2012-07-19 15:05:28.67 spid52      Starting up database 'A2010_20120719'.
    2012-07-19 15:06:28.19 spid52      Starting up database 'A2010_20120719'.
    2012-07-19 15:06:54.15 spid55      Starting up database 'A2010_20120719'.
    2012-07-19 15:53:49.09 Server      Server resumed execution after being idle 1227 seconds: user activity awakened the server. This is an informational message only. No user action is required.
    2012-07-19 15:53:50.27 spid53      Starting up database 'A2010_20120719'.
    2012-07-19 16:44:18.56 Server      Server resumed execution after being idle 1672 seconds: user activity awakened the server. This is an informational message only. No user action is required.

    Therefore this indicates that your database has to keep re-openning, indicating your AUTO_CLOSE property is turned on.  Or database is being shutdown.  Please read this article t get further understanding of SQL Server Express Edition and Idle Timeouts.

    http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

    Thanks.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    • Marked as answer by Karbono Friday, July 20, 2012 9:47 AM
    Thursday, July 19, 2012 6:46 PM
  • I'll take a look when I get back to work tomorrow... but this database's settings are exactly the same as the ones on the older database. Shouldn't they both have the same problem, then?

    plus, it keeps going down even if I keep trying to work on it without giving it enough time to timeout.

    ...and isn't it natural that it "wakes up" if I go back to it after it crashes?
    • Edited by Karbono Thursday, July 19, 2012 7:13 PM
    Thursday, July 19, 2012 7:05 PM
  • I turned off auto-close and I'm having no issues with the database. Thank you so much!

    I just wish I knew why the old backup didn't give me any problems, even with auto-close set to on.

    Friday, July 20, 2012 9:47 AM
  • Glad it worked out, I'll do some testing with Express Edition to test the behavior of Auto Close... i'll put it up on my MSDN blog and SQLCAN blog later this month :).

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Friday, July 20, 2012 4:44 PM