[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


How to shut down a SQL Server ?

Posted on 2004-09-29
Medium Priority
Last Modified: 2008-02-01

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,

Question by:fischermx
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
LVL 15

Accepted Solution

jdlambert1 earned 1500 total points
ID: 12185898
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.

Expert Comment

ID: 12186053
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

Expert Comment

ID: 12186677
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' "
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Expert Comment

ID: 12189530
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.

LVL 15

Expert Comment

ID: 12190147
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).

Author Comment

ID: 12190242
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 ?

LVL 15

Expert Comment

ID: 12190299
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.

Author Comment

ID: 12190724

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 ?

LVL 15

Expert Comment

ID: 12190789
Yeah, you're right, fischermx, it's with NO_WAIT.

Expert Comment

ID: 12190815
Just issue a checkpoint before you execute the stop request.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question