question

Therealist avatar image
Therealist asked

What Happens when a sql server restarts?

What kind of things happen or occur when a sql server restarts?..like creating new errorlog,rolling back uncommitted transactions,rolling forward etc.
serverrollbacktrace-flags
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Everything that happens is logged in the SQL Server logs. Exactly what happens is determined by : - the version/edition of SQL server that you are running - startup swtiches - trace flags - installed features - configured options - state of databases when the server went down - probably lots more I'd suggest you look at an example log from your own server and piece together the understanding from there
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ha! I'm sitting here typing and typing and there you are giving a good answer while I did it.
1 Like 1 ·
Grant Fritchey avatar image
Grant Fritchey answered
I don't know that I've seen it all written down in one location. I'm also not sure I can summarize it all. My best suggestion is for you to get a copy of Kalen Delaney's [SQL Server Internals][1] book. You can sort of walk yourself through it. SQL Server will have to work with the OS to allocate memory and take on any CPU affinity you've set. The recovery process will roll forward or roll back transactions in order to maintain the durability of the databases. Internal memory allocations will occur. Any start up switches will be observed. SQL Agent may be started with the server if you've configured it that way (a good idea). Internal memory gets allocated. The system_health extended event session starts for 2008 & above. Any other extended event sessions designed to begin on startup will kick off. That's all I can think of off the top of my head. I checked the internals book. It doesn't have a "start up process" outlined. But, chapter 1 gives you a very good architecture overview that does include a lot of discussion around the fundamental internals. That will help. I also checked Chris Bolton, et al, book [SQL Server 2008 Internals and Troubleshooting][2]. It doesn't have a start up description either, but approaches the whole thing from a slightly different point of view, so is worth a read too. One more thing to look at is the error log. It shows a series of events that occur on start up. That can also help to answer your question. [1]: http://www.amazon.com/Microsoft-Server-Internals-Developer-Reference/dp/0735658560/ref=sr_1_1?ie=UTF8&qid=1415963379&sr=8-1&keywords=sql+server+internals [2]: http://www.amazon.com/Professional-Server-2012-Internals-Troubleshooting/dp/1118177657/ref=sr_1_3?ie=UTF8&qid=1415963379&sr=8-3&keywords=sql+server+internals
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

:) And you gave a good answer too.......eventually
3 Likes 3 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.