Solved

How to shut down a SQL Server ?

Posted on 2004-09-29
10
2,663 Views
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,


0
Comment
Question by:fischermx
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 12185898
From SQL Server Books Online:

SHUTDOWN
or
SHUTDOWN WITH NOWAIT

Arguments
WITH NOWAIT

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.

Remarks
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.
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12186053
I have a scheduled task execute this nightly for my NAS implementation. This ensure I get a good snapshot.
net stop SQLSERVERAGENT
net stop MSSQLSERVER


REM start SQL server with trace flag on
net start MSSQLSERVER /d"F:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" /T1807
net start SQLSERVERAGENT
0
 

Expert Comment

by:EnniK
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' "
0
 
LVL 4

Expert Comment

by:willcode4coffee
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.

M@
0
 
LVL 15

Expert Comment

by:jdlambert1
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).
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:fischermx
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 ?


Regards,
0
 
LVL 15

Expert Comment

by:jdlambert1
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.
0
 
LVL 1

Author Comment

by:fischermx
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 ?

Regards,
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12190789
Yeah, you're right, fischermx, it's with NO_WAIT.
0
 
LVL 5

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now