Sql-server – Stopping SQL Server Agent: Effect on runnings jobs

sql serversql-server-2008ssis

On a server with MS SQL Server 2008 installed, we use the SQL Server Agent to schedule a large amount of SSIS-packages, all with unique schedules throughout the day.

Now and then, the server needs to be restarted (patching etc). Is it safe to right-click on SQL server agent and select 'stop'? The SSIS-packages are not that well-built and if they are stopped right in the middle of something data might become corrupted.

So, does stopping SQL Server Agent mean that jobs are 'killed', or is it just so that no more jobs will be scheduled to run from that moment?

Best Answer

  1. Yes, jobs are aborted. Non-SQL process (eg CmdExec) are aborted too.
  2. No more jobs run until SQL Server Agent is started again. Missing scheduled jobs will not be run.

After comment:

SQL Server Agent is just another connection to the DB engine. It will send an abort to the DB engine and all SQL jobs are stopped.

Abort means something here: it is a specific client action. See SQL Server Transaction Timeout

Related Question