• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2958
  • Last Modified:

How to shut down a SQL Server ?

We have a database with external users in a server that needs to be restarted due the instalation of another software.

I need to know which is the proper way to shutdown the server without causing any data loss to current transactions in progress.
I also need to know if the method recommened will wait all users to logout or not, or it will just wait to current transactions to finish/rollback on their own.

Also, it is needed to shut down also the SQL Server service ?

Thanks in advance,

  • 4
  • 2
  • 2
  • +2
1 Solution
From SQL Server Books Online:



Shuts down SQL Server immediately, without performing checkpoints in every database. SQL Server exits after attempting to terminate all user processes, and a rollback operation occurs for each active transaction.

Unless members of the sysadmin fixed server role specify the WITH NOWAIT option, SHUTDOWN tries to shut down SQL Server in an orderly fashion by:

Disabling logins (except for members of the sysadmin fixed server role). To see a listing of all current users, execute sp_who.

Waiting for currently executing Transact-SQL statements or stored procedures to finish. To see a listing of all active processes and locks, execute sp_lock and sp_who.

Performing a checkpoint in every database.
Using the SHUTDOWN statement minimizes the amount of automatic recovery work needed when members of the sysadmin fixed server role restart SQL Server.
I have a scheduled task execute this nightly for my NAS implementation. This ensure I get a good snapshot.

REM start SQL server with trace flag on
net start MSSQLSERVER /d"F:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" /T1807
A better method may be to deny users login priveldges before executing the shutdown command.
to deny login you would use this command

- sp_denylogin [ @loginame = ] 'login'

If all users are connected through one account or one group account this would close their connections once the connection is idle.
Then when you issue the SHUTDOWN command all transactions would be finalized and commited. This would commit all data and make sure all users have ended their connection.

You could also just use the sp_denylogin to lock out all users while an admin cleans up the database or makes changes to it.

From the looks of it...you are issuing a job from the command prompt.  If you run the following commands instead...you can make sure no one is using the server before shutting it down.  Then do the reverse...using sp_grantlogin 'username' after net start to grant users access again.

net send /users "Database services are shutting down. Connection will be restored in ?? minutes."

OSQL -U login_id [-P password] ( or –E for trusted) -S server_name -d db_name -q "sp_denylogin 'User' SHUTDOWN"

net start ....
net start....

OSQL -U login_id [-P password] ( or –E for trusted) -S server_name -d db_name -q "sp_grantlogin 'User' "
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Using SQL Service Manager you should "Pause" the server. This will allow current active connections to continue, but will not allow any new connections. Once all active connections have been dropped, you can run a checkpoint to clean up any uncommitted transactions. Finally, shut down SQL Server (by clicking the Stop button in SQL Service Manager) and then reboot.

This should insure that you don't lose any transactional data.

Also, simply right-clicking the SQL Server with Enterprise Manager and choosing Stop, or hitting the stop button on SQL Server Service Manager will do the same as SHUTDOWN (turn if off after waiting for currently executing Transact-SQL statements or stored procedures to finish).
fischermxAuthor Commented:
Thanks for all, I think my concern was not in the original question.

My doubt come, because I was told that my server has been shutdown by the IT guys who knows a bit about SQL Server. I always use shutdown. When I have to get ride of users due some database model change, and there's no hurry, I perform similar procedure to the one described by Ennik.
But I'm not sure what's the effect of the windows' shutdown or the just turn off the computer (like a power outage).

1) What happens to current transactions if I just shutdown windows ?

2) What happens to current transactions if the computer is just turned off ?

1. SQL Server will try to do a clean shutdown, waiting for current transactions to finish. If it can't, the Windows shutdown will hang.

2. When SQL Server is restarted, every transaction that was commited prior to power-off will be fine. Every transaction that was not committed will be rolled back, as if it had never started.
fischermxAuthor Commented:

jdlambert1 :

The question 1 is precisely the more tricky. I prepared a test yesterday after I send the question. In a spare server I setup a 1 million record test table with a couple fields, I made an estimated that updated one one column for all records with a string literal would take about 4 minutes.
Then I ran the experiment, I started an update on that table from a second computer. After it passed about 2 minutes (right in the middle), I shuted down windows from the start menu. I took about 15 seconds to shutdown, so, it didn't hang.
When it rebooted the server, the update wasn't there !, the transaction was rolled back, so, it seems that a Windows shutdown, sends a SHUTDOWN NO_WAIT to SQL Server ?? What do you think ?

Yeah, you're right, fischermx, it's with NO_WAIT.
Just issue a checkpoint before you execute the stop request.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now