0

I have an application that uses a sql server db (SQL Express 2008 R2) and starts when Windows OS starts. When the pc shutdown unexpectedly and then restart the pc the application run automatically but it' can't query the DB I looked for the error log and it shows no record for starting the DB and I have to open the SSMS and query the DB. Only after that the db will start (even after seconds from starting the OS)

2018-02-28 13:12:51.05 Server      Server process ID is 2420.
2018-02-28 13:12:51.50 spid7s      Starting up database 'master'.
2018-02-28 13:12:55.60 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2018-02-28 13:12:58.87 spid52      Starting up database 'ReportServer$SQLEXPRESS2008R2TempDB'.
2018-02-28 13:35:27.38 spid57      Starting up database 'DBName'.

as you can see there are about 23 minutes between starting master and my db. I also checked the service and it's running fine in the OS start.

I have 2 questions:

1- What SQL Server make to start the db and can I do that programmatically?

2- I noticed that starting 'ReportServer$SQLEXPRESS2008R2TempDB' is the beginning of the delay and it may be the cause of the delay, so can I change the order of starting the databases on the server?

Thanks in advance

Improve this question
8
  • Do you find Recovery is complete. This is an informational message only. No user action is required. somewhere in the error log? Maybe your database is recovering after the unexpected shutdown
    – Tom V
    Feb 28, 2018 at 11:52
  • @TomV Yes, I'll update the post with it
    – Ahmed
    Feb 28, 2018 at 11:54
  • You should look for other messages in errorlog such as <n> transactions were rolled forward / rolled back in database <...>. This is crash recovery in action. See more detailes here: books.google.it/…
    – sepupic
    Feb 28, 2018 at 15:27
  • @Ahmed How big is that Tempdb?
    – LowlyDBA - John M
    Feb 28, 2018 at 16:57
  • @LowlyDBA, Sorry for the late replay, the Tempdb is 2.75 MB and if you mean the ReportServer$SQLEXPRESS2008R2TempDB it's 4 MB
    – Ahmed
    Mar 4, 2018 at 7:46

1 Answer 1

Reset to default
2

Your DB has AUTO_CLOSE ON (which is typical of SQL Express instances, like yours). It means that the database is not opened until first accessed. This is when the first mention of it appears in the SQL ERRORLOG file.

The delay of 23 minutes is because your application is not opening the database. If you think this is a problem, it must be in your app. Are you sure your app, which must be an NT service, is configured to auto-start on OS boot?

Improve this answer
2
  • No, the Auto_Close is OFF
    – Ahmed
    Feb 28, 2018 at 12:31
  • 2- I'm looking for the SQL Server opening of the database or making it ready to accept queries ' Starting up database DBName ', 3- Yes the application is configured to start with the OS start and if windows shutdown correct it starts fine on the next startup, the problem on the un-expected shutdown
    – Ahmed
    Feb 28, 2018 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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