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,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 are issuing a job from the command prompt.  If you run the following commands 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' "
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.