I'm running SQL Server 2008 R2 and have it set to start the service in the background on startup of the machine. When this happens, what is the process that it goes through to startup?

I believe that the master database loads first, but what order do the other databases load in? How is this determined?

EX: I have databases a1,b1,c1. Does a1 load before c1?

How would I go about setting the priority of a database (e.g. load c1 before b1...)?

I'm looking for a behind-the-scenes look into what happens when the program starts, I am not running any queries on it. Thanks.

  • 1
    What is your goal? what is the diffrence if A1 db load before B1 db? also the server will be running after all those process terminate.
    – Ilyes
    Oct 30 '17 at 17:42
  • 4
    A lot of startup information is logged in the error log when SQL Server starts. Perhaps reading through the contents may help in your effort.-->USE Master GO EXEC XP_READERRORLOG GO
    – Ross Bush
    Oct 30 '17 at 17:42
  • The goal is to determine what the priority levels of the db are. I think that both a1 and b1 are loading at the same time and slowing both of them down. I need for a1 to load first (or at least load much faster than b1). b1 is just a larger db is all.
    – CuriousOne
    Oct 30 '17 at 17:52
  • The "order" doesn't make any difference. Databases don't load into memory or anything like that. The data lives on the disk and can be accessed. It doesn't matter if a database is larger than another one, the sql engine uses the same resources. What is the real issue you are facing??
    – Sean Lange
    Oct 30 '17 at 18:15
  • In the logs there's a message stating "Starting up database 'a1'." and it does this for all the available db. Apparently, the reason why a1 starts slower than b1 is because a1 doesn't login on the first try. The real issue is that I have to start Apache Tomcat after sql starts, but it throws an error because the a1 db isn't ready yet.
    – CuriousOne
    Oct 30 '17 at 18:40

Ah, after you updated your comment I realized that you were asking about how to start another process after all databases have been restored/loaded. You can configure a stored procedure to be called using the sp_procoption. I am pretty sure that runs after all databases are ready. Next, you just need to invoke your external process via sql server.

"...Execution of the stored procedures starts when all databases are recovered and the "Recovery is completed" message is logged at startup...."

Your Answer

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