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' "
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 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