Many a system administrator has been faced with the task of rebooting a server during the middle of the working day. This timing is the least desired option, as server reboots are often done during off-hours and weekends. But there are times when reboots are needed during the day.
Reboots during the day get a bit uneasy when the server is a database server because most end-users would rather chew tinfoil than have a database server go offline for even five minutes during the day, interrupting their work. In an ideal world there would be a way for you to (1) allow end-users to finish their work and (2) not allow any new users to connect to the instance until the reboot is completed.
Turns out such an option already exists, and it’s been right in front of our nose the entire time:
By pausing the SQL Server service before restarting the instance we allow end users to continue their work uninterrupted and we also stop any new connections to the instance. This is a nicer way of telling people to “get out” of the database in order for the server to be rebooted. I wouldn’t leave the server paused for 60 minutes of course, but I would rather use this method than forcibly disconnect users and rollback their transactions.
When a server is paused you will see messages similar to this in the SQL Server error log:
Error: 17142, Severity: 14, State: 0. SQL Server service has been paused. No new connections will be allowed. To resume the service, use SQL Computer Manager or the Services application in Control Panel. Error: 18456, Severity: 14, State: 13. Login failed for user ''. Reason: SQL Server service is paused. No new connections can be accepted at this time. [CLIENT: <local machine>]
Next time you are worried about rebooting during the day think about the pause button instead. It might be a nice compromise for your end-users.
Be aware that when you restart SQL Server it does not checkpoint so that it goes through crash recovery. This lengthens downtime. This is a bug: https://connect.microsoft.com/SQLServer/feedback/details/776427/sql-server-service-control-manager-shutdown-does-not-checkpoint
Good to know. Notice, the screen shot is SQL Configuration Manager, not Service Control Manager, which is the way I have always heard you should manage SQL Services. However, given the connect item, I wonder if simply stopping SQL from Config Manager post pause makes some sense, any thoughts Tom?
I doubt config manager does anything different from shutting down the service. There is a Windows API for that that everything uses.
Config Manager can’t even log in to issue a SHUTDOWN.
I think that’s right, that it is the same API used by both, but config manager is specific for administering the SQL instance (thus it has things like FILESTREAM tabs there) where the service control treats SQL like any other service (log on, what to do if service doesn’t start, etc.)
I use config manager for interacting with SQL Server, AFAIK that is the best way to handle all admin tasks related to the SQL Server service(s).
Thanks for the link, but I don’t see how that has anything to do with pausing the instance. Did I miss something?
No, no pausing. Just wanted to warn about that problem because it lengthens downtime by about 1min on most instances. This is the time that SQL Server explicitly targets.