LONG LONG - 1 month ago 16
SQL Question

Restart entire server by using batch in Execute Process task in SSIS

I have two scheduled SSIS jobs in sql server agent on our production server. One for rebooting production server itself (lets name it 'A'), and the other one for rebooting another server (lets name it 'B')

I am simply using Execute Process Task in SSIS which will execute certain batch file. And following is the batch command:


shutdown /r /m \\A /t 300
note: 'A' is the full name of the production server, and for another server, batch command is same only 'B'would replace 'A' in that command.


The job that to reboot Server 'B'(not production server 'A') could be executed without any issue at scheduled run time, however, for rebooting ITSELF (production server 'A'), job always fails. And here is the error message (I could hardly find any related to this issue)


The Process exit code was "5" while the expected was "0"


I tried to remote to 'B' server and run the package on local 'B', still good. Also, I tried to run that batch file without either SSIS or SQL Server Agent, it was still good. So far the only thing I did not try yet is to run SSIS package locally to reboot 'A' because 'A' is not allowed to be offline during work days. (But I am guessing running SSIS package locally on 'A' server would not give me any problem)

I think it is kind of sql server agent on 'A' that block rebooting the server that is currently running on?

Any ideas or suggestions, guys? Thanks in advance :)

Answer
  1. It is almost certainly a bad idea to have a scheduled (weekly or otherwise) reboot of SQL Server (including the server it is running on). If there is not an extremely good reason for doing this, it should be stopped.

  2. Using SSIS for nothing other than running OS-level commands / CMD scripts / etc is grossly over-engineered. Better options are:

    1. Windows Scheduled Tasks
    2. Use a SQL Server Agent job with an Operating system (CmdExec) job step.
    3. Use a SQL Server Agent job with a Transact-SQL script (T-SQL) job step that execute xp_cmdshell.