Starting and Stopping SQL Server Part 1
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/startingandstoppingsqlserverpart1.asp
-
Andy
Do you know a way of automatically running a script before SQL is stopped i.e. a pre-sql shutdown script?
-
the t-sql shutdown command worries me! -what if someone could enter the command with some sort of sql injection! Could this be disabled in some way?
-
It's a shame that the author didn't take into consideration the fact that a SQL Server might be clustered.
In that case the only correct way of stopping the server is to use the cluster administrator tool. All methods mentioned in the article would create problems (server would try to restart).
-
My favorite way is SC command, works on local or remote box, very fast. This tool should totally replace old NETSVC in anyone's toolkit, much more powerful and much faster.
-
Thanks for your comments so far!
- There is no way to disable the shutdown command that I know of. Using it requires the user to be in sysadmin or serveradmin roles - if you're running code under either of those you have far greater worries than shutdown!
- Dont know of a way to run a preshutdown script. Maybe...there is a DMO event, but it doesnt have many and dont think that is one. Very interesting idea, if someone has info on this I'd enjoy hearing it
- You're right that I left out the clustering shutdown. Depending on how many things I omitted I may add a 5th article to the series(yes, there are four already!) to regroup on those items.
-
Shutting down SQL is easy, it is shutting down replicated servers in sequence and then starting them up again - that is an art! If anyone has a process / article where they discuss this I would be a happy DBA!!!!
-
Hi,
Sql Junkie
Posted a question yesterday athttp://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=237536
asking how to make SQL Server Agent to post a message that the job is running when someone is trying to shut it down.
This is the same kind of question if we can run scripts on shutdown or shutdown attempt. I know that we can do that when we write services (not me personally, but other developers):
".........You use the OnStop method to handle the Stop event by specifying the tasks that you want your service to perform when it is stopped......." This is from the Microsoft exam prep book for exam 70-310
The question is: how to implement OnStop method for the services written by someone else (Microsoft for example)
Regards,Yelena Varsha
-
James, there are really no worries around replication. Replication will maintain transactional consistency and pick back up again when the agent is restarted. More difficult is the scenario where one of the servers involved is down, then you start to accumulate a lot of transactions in the distribution db (for transactional repl) that tie up a lot of disk space until distribution resumes or the subscription expires. Have you run into a specific case where you had problems?
-
When using the command prompt, and when the default dependency exists between SQL Server and SQL Agent, I most often use the commands:
net stop mssqlserver /y
and
net start sqlserveragent
Cheers,
- Mark -
Since there's several services that need to be started/stopped (depending on which ones I want to have running for this particular server), I always create two .BAT files to run from the command line and to create links on the desktop. These are for 2005, but similar works for 2000 of course:
Start script
============
@REM Not starting/stopping SQL Server Browser, SqlServer Active Directory Helper
net start MSSQLSERVER
net start MSSQLSERVEROLAPService
net start MsDtsServer
net start SQLSERVERAGENT
rem net start ReportServer
rem msftesql is automatically started (but not stopped) by SQL server
rem net start msftesql
pause
Stop script
===========
@REM Not starting/stopping SQL Server Browser, SqlServer Active Directory Helper
net stop /y msftesql
net stop /y ReportServer
net stop /y MSSQLSERVEROLAPService
net stop /y MsDtsServer
net stop /y SQLSERVERAGENT
net stop /y MSSQLSERVER
pause
Kristian Wedberg
-
<<In that case the only correct way of stopping the server is to use the cluster administrator tool. All methods mentioned in the article would create problems (server would try to restart).>>
That is only true of SQL Server 7.
SQL Server 2000 (and, I assume, 2005) is cluster aware. So, none of the described methods will cause a failover to initiate. A shutdown from Enterprise Manager, the Service Control Manager, the command line of TSQL will all just take the clustered resource offline. The same can be done through the Cluster Administrator.
- Jay
-
Although my query is not exactly related to start/stop of SQL server, it involves stopping a SQL database within a server instance.
If I want to detach a database from an instance, I first have to ensure that there are no clients attached to that database. I have implemented a routine which looks up the sysprocesses table and KILLs each connected client. The issue however is that how do I "lock" out the database, so that no further connections are made while I am detaching the database. It so happens that in my environment there are certain apps, which automatically reconnect to the database once they get disconnected. Hence, while I am on the KILLing spree
some other client connects to the database.-Krishnan
-
Using the "net start sqlserver" command, is there any way to specify which instance. I believe this will only start the default instance, or?
-
Each instance has a unique service name. Named instances are named something like MSSQL$MyInst.
So, just run NET START MSSQL$MyInst from the command line.
- Jay
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply