How to remotely Start/Stop SQLServer services kicking off existing connections?

Question

I know there is already a question about this but my issue is more oriented to remote scenarios.

With net start/stop you can specify a /y parameter to bounce users off current sessions but you cannot start/stop remotely.

With sc you can start/stop remotely but if the SQLServer instance is being used it won't let you stop the service (with a "[SC] ControlService FAILED 1051: A stop control has been sent to a service that other running services are dependent on." message)

So my question is: what's the best way of kicking out users stopping the SqlServer service remotely?

Answer 1

I think the /y just answers Yes to the "Are you sure?" prompt. I'd think that sc could be used as well, though it may time out waiting for the service to stop if there's a lot of inflight transactions. Does it give you any specifics of why it can't stop?

Here's a couple of other methods to stop a remote SQL instance. Except for SHUTDOWN WITH NOWAIT, I'd recommend any of them.

• psexec will let you run net stop remotely.
• There's also the SQL SHUTDOWN command - you can issue that WITH NOWAIT to avoid waiting for current transactions to finish and checkpointing, which will make shutdown faster (but subsequent startup slower, and could lead to lost data).
• Or you could use either Configuration Manager or Management Studio to stop a remote instance.

Edit: The error is pretty self explanatory. It means you must stop dependent services first. Sql Agent is probably at least one of them. Checking Admin Tools->Services will show you the rest.