March 5, 2011 at 11:56 am
Hi,
I have restarted the windows machine in which ms sql server database has been installed and took much time to recover the databases on the machine when database services started since i have restarted windows without stopping database services .
Can guys tell me which approch would be good and how to handle such a suituation ?
How database services treat such a restart (means shutdown or shutdown with nowait ) ?
Thanks in Advance.
March 5, 2011 at 12:41 pm
After a shutdown like that SQL has to run crash recovery on all databases. If they were very busy, that can take some time. If you can run checkpoint before shutting down
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2011 at 3:30 pm
The SQL error log shows the progress of the start up recovery and if it was able to roll transactions forward or backward for each database.
Large databases will take a while, but they are probably all done by now since it's been a few hours.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 6, 2011 at 2:08 am
Thanks for your reply.
Here my requirement is , i want to restart the windows machine in which ms sql server is running which one of the following would be best approach and why ?
1. stopping the ms sql server services and restarting the windows machine.
2. Restarting the windows machine without stopping the ms sql serverr .
March 6, 2011 at 2:21 am
1. stopping the ms sql server services and restarting the windows machine.
2. Restarting the windows machine without stopping the ms sql server .
Option 1 is fine. When you restart Windows, the SQL Server service shuts itself down prior to the system shutting down and restarting. Look in the SQL Server log, you'll see something like this when you shut down Windows:
2011-03-05 15:46:55.31 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 6, 2011 at 2:52 am
Also, stopping the SQL services properly will cause SQL server to perform a checkpoint before fully stopping, so this could take quite a while depending on several things.
If you just shut windows down, windows will 'tell' SQL to shutdown, which, in turn, will begin a checkpoint. However, if the checkpoint takes too long, and especially if you are forcefully shutting the system down or specifying NOWAIT, then windows may kill the SQL process before the checkpoint completes. This could cause the start up recovery process to take longer.
So it is best to CHECKPOINT, stop SQL services, once stopped, reboot.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 6, 2011 at 3:12 am
... then windows may kill the SQL process before the checkpoint completes.
Not windows, but the user, as per this page here.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 6, 2011 at 4:37 am
Ray Mond (3/6/2011)
1. stopping the ms sql server services and restarting the windows machine.
2. Restarting the windows machine without stopping the ms sql server .
Option 1 is fine. When you restart Windows, the SQL Server service shuts itself down prior to the system shutting down and restarting. Look in the SQL Server log, you'll see something like this when you shut down Windows:
2011-03-05 15:46:55.31 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2011-03-05 15:46:55.31 Server SQL Server is terminating because of a system shutdown -- Is sql server terminating equivalent to SQL Shutdown with Nowait option?
March 6, 2011 at 4:54 am
Is sql server terminating equivalent to SQL Shutdown with Nowait option?
No, that wouldn't be good for startup recovery. If you run this:
DBCC TRACEON(3502, -1)
SQL Server will then log checkpoints in the error log. You can then experiment with all the different ways of shutting down the service, and see when checkpoints are performed.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 6, 2011 at 5:32 am
Thanks Ray mon for your suggestion.
It would be great if you provide some links where i can find what could happen if windows system restart without stopping the database services.
March 6, 2011 at 7:37 am
I don't have any links to documents that specifically address what you are looking for. To be honest, I've never really thought about it, but I guess I trust Microsoft to do the right thing here, since they own both platforms i.e. Windows and SQL Server.
However, you can see for yourself that the SQL Server service does perform the necessary checkpoints before it shuts down using the trace flag I provided earlier. The only time it doesn't perform the checkpoints is when you explicitly issue the SHUTDOWN command with the NOWAIT option. At least that's what I see.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 6, 2011 at 8:09 am
Ray,
I was not referring to option 1 - the user directly stopping the sql service.
In the example that I was referring to (option 2), I noted that 'you' started the whole process - being the user. However, since in this example, it was not the user themselves who sent a WM_SHUTDOWN/ WM_CLOSE message directly to the SQL server service (which triggers SQL server to begin the checkpoint and stopping services) then I referred to this message chain as the close message coming from windows (which in turn came from the user). This is the complete difference between option 1 and 2; windows telling SQL server to to close (which SQL server begins to checkpoint then close), rather than the user himself stopping the service.
Also, I think you might have been thinking of a typical Start->Shutdown sort of option, which is gone from most modern versions of Windows Server OS's from a RDP session, which would trigger a windows popup of sorts to query the user about forcing apps to close. But I was thinking more about shutdown.exe where the user tells shutdown.exe ('windows') to possibly force close after a delay (depending on the command line switches).
Had I described it as you edited, it would have confused that key step in the command chain with option 1 - the user directly initiating the stopping of the SQL service.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 6, 2011 at 8:42 am
Actually, I meant to write that doing option 2 was fine, and was actually referring to option 2 all the while.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 6, 2011 at 9:46 am
Ray Mond (3/6/2011)
Actually, I meant to write that doing option 2 was fine, and was actually referring to option 2 all the while.
And I take that back, if your objective is to get all dirty pages written to disk. Seems like my trust was misplaced. Jim's right, if you want to ensure that all dirty pages are flushed to disk prior to your system shutting down, you should manually shut down the SQL Server service first. I had always thought that the SQL Server service would use the SERVICE_ACCEPT_PRESHUTDOWN flag to ensure that it will have enough time to shut itself down gracefully, regardless of how the system was shut down (other than a hardware failure). Upon further investigation, this was not the case, at least not on the SQL Server 2005, 2008 and 2008 R2 services I looked at.
So while the system will shut down ASAP, the SQL Server startup recovery process will take longer, which will in turn depend on your recovery interval setting for one.
And Jim, thanks for the primer on Windows shutdown processes.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 6, 2011 at 11:27 am
Ray,
Thanks for bringing out the preshutdown.
Technically, at the end of the day, there is no difference to the recoverability of the data, just when the finalizing of the data in the mdf occurs - at checkpoint pre shutdown or in recovery on startup. So this whole discussion is splitting hairs.
Having said that, Microsoft has been known to occasionally require a service pack and a hot fix or two [hundred], so I just feel more comfortable ensuring it is done not as a recovery step, if I am able to control the shutdown process myself.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply