shutdown SQL Server

Last Post 17 Dec 2010 08:50 AM by russellb. 16 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
river1
New Member
New Member

--
10 Dec 2010 04:39 AM
Hi Masters,

  I have a client that as no UPS and want that it's SQL Server Computer (Server) closes autommatitly at 16:00h, every day.

Do you masters have any ideia how to do that?


Can i make a task of windows to do this? what about if SQL Server still have people connected?


Thank you
gunneyk
New Member
New Member

--
10 Dec 2010 06:37 AM
You can schedule a windows task to execute a SHUTDOWN command at that time. If you look up SHUTDOWN in BooksOnLine it will give a description of what happens when it is called but essentially it will wait until the currently running transactions are complete and then shuts down. But you will still have to start it up each day as well.
river1
New Member
New Member

--
16 Dec 2010 07:39 AM
Hi master,

   My shutdown command to shut the windows down is this:


    Batch file, located on c: that is executed by a windows task at 16:00h

    command:  Shutdown –s –f –t 0 


    There is no problem to shudown the SQL Server Server with this command?


Thank you
gunneyk
New Member
New Member

--
16 Dec 2010 08:06 AM
Well I can't say for sure without some investigation but I was talking about the TSQL SHUTDOWN command. The Shutdown at the windows level will shut down the entire server will it not? I thought you only wanted to shut down SQL Server. If you do I would advise shutting down SQL Server first.
river1
New Member
New Member

--
16 Dec 2010 09:33 AM
Ok, so you think that is better to close first the SQL Server.

I will see if i can find any code to do that.

I will post the code here, if i have any dought.

Thank you master gunneyk
russellb
New Member
New Member

--
16 Dec 2010 11:17 AM
There shouldn't be a problem just issuing a windows shutdown command, but I recommend 1st issuing a net stop mssqlserver. This will ensure that a CHECKPOINT is written to every database. 

so your batch file will look like this:

-----------------------------------------------------------
net stop mssqlserver
Shutdown –s –f –t 0
------------------------------------------------------------
gunneyk
New Member
New Member

--
16 Dec 2010 01:42 PM
Pedro, you asked initially about if there were users still in the db when you shutdown and that is why I suggested the TSQL SHUTDOWN command. It specifically states that it will wait for them to finish their current command where as I believe a NETSTOP will kill any open trans so they will be rolled back. I am not 100% sure but that is my understanding. So you may want to decide which works best for your needs.
river1
New Member
New Member

--
16 Dec 2010 02:49 PM
Thank you gunneyk.

I don't want the command to wait until the last users finishes it's transaction...
What i want is that if someone is working on the database when the net stop is issued, that the database does not become corrupted (suspect).

So, my question is:

If i issue a net stop to SQL Server, all the users that are connected will be disconnect, but the database will be all right ? or can the database become suspect because the user was disconnected?

Thank you Master
russellb
New Member
New Member

--
16 Dec 2010 03:35 PM
Killed transactions will be rolled back, checkpoints issued. Your database will be in a consistent state.
gunneyk
New Member
New Member

--
17 Dec 2010 06:34 AM
What Russell said .
river1
New Member
New Member

--
17 Dec 2010 06:42 AM
Thank you masters Russel and Gunneyk.

I have one more question, maybe you can help.


I have created the bat file with this code inside:


Net stop SQLAGENT$PR2005
Net stop MSSQL$PR2005
shutdown -s -f -t 0

(the code is in a single bat file, named shutdown.bat)


When i execute this bat file, SQL Server is first sgutdown and only then the OS is shutdown.


Bu t when i power on the machine i receive a black bord (command prompt) with this on the caption:

c:\program files\microsoft sql server\mssql.1\mssql\bin\sqlagent90.exe



The Sql Server is good, but i don't understand why this command prompt is diplayed.


Every time i try to close it, it starts again....


Why is this happening?

Thnak you


russellb
New Member
New Member

--
17 Dec 2010 06:51 AM
Are you running the batch file from a windows task or a SQL Agent job?

Is SQL Agent service set to AUTOMATIC startup?

Is there any action performed (you can check in the services applet) when SQL Agent starts?
russellb
New Member
New Member

--
17 Dec 2010 06:52 AM
By the way, since there is no UPS please be certain you are getting good backups daily -- which you sholuld be anyway. If you were taking the backups after 16:00 don't forget to reschedule them to run (and finish) before 16:00.

Disk failures are more common on shutdown/startup then while running live. Particularly on older hardware.
river1
New Member
New Member

--
17 Dec 2010 08:14 AM
"Are you running the batch file from a windows task or a SQL Agent job? "

I have created the bach file and executed it from my desktop , but i will put it in c: and run it from a windows task


"Is SQL Agent service set to AUTOMATIC startup?"

Yes

"Is there any action performed (you can check in the services applet) when SQL Agent starts?"

The SQL Server agent as no addition tasks when it starts, it is what SQL Server does by default
russellb
New Member
New Member

--
17 Dec 2010 08:26 AM
Ok, try this. Open it in the Services Applet, go to the logon tab and make sure the box "Allow service to interact with the desktop" is NOT checked.
river1
New Member
New Member

--
17 Dec 2010 08:35 AM
Hi master,

i unchecked the "Alow service to ............" and the problem as fix.

Thank you very much.

Why does this happens, after i run the bach?
russellb
New Member
New Member

--
17 Dec 2010 08:50 AM
I don't know. I would guess that happened somehow when you double-clicked the batch file on the desktop...but again, not 100% sure.


Acceptable Use Policy
---