restart agent / not use xp_cmdshell

  • I'd like an autoproc routine to start up the Agent in case SQL Server was stopped and started. I cannot use xp_cmdshell as we're not allowing this per auditors. I've seen some permissions issues/questions from people if you try to use

    EXEC xp_servicecontrol N'START',N'SQLServerAGENT'

    What is a good method to get the Agent started as part of an autoproc?

    TIA,

    Doug

  • Right click on the SQL Server Agent and choose properties. Choose the option as shown in the attachment. (See the attachment.)

    Does that help?


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Bru,

    No. If you stop and start the sql service, the system doesn't treat that as an unexpected stop so those settings do not take effect.

    Doug

  • This is what I use, beleieve I got it either here or another forum site:

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[AutoStart_SQLAgent] Script Date: 12/17/2009 15:37:11 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROC [dbo].[AutoStart_SQLAgent]

    AS

    BEGIN

    DECLARE @Err int, @Msg varchar(100), @ServiceName sysname

    SET @ServiceName = 'SQLServerAgent'

    EXEC master.dbo.xp_servicecontrol 'START', @ServiceName

    SET @Err = @@ERROR

    IF @Err = 0

    BEGIN

    RAISERROR ('Successfully started SQL Server Agent', 1, 1) WITH LOG

    END

    ELSE

    BEGIN

    SET @Msg = 'Error occured while starting SQL Server Agent. Error code: ' + STR(@Err)

    RAISERROR (@Msg, 18, 1) WITH LOG

    END

    END

    exec sp_procoption

    @ProcName = 'AutoStart_SQLAgent',

    @OptionName = 'startup',

    @OptionValue = 'on'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply