Haven't you dreamt about a way to prevent the change of some parameters by some people that still have system administrators privilege and that can seriously put in danger your SQL Server and applications running on them.

I know I have this nightmare.

I work for a software company and one of my tasks is to play the support level 3 role (last level) of over 500

SQL Server installations. How many times I have had to troubleshoot some problems at a customer site and finally

find out that someone had played around with some SQL Server settings. If you are looking for a way to keep control over some SQL Server settings while you are not there here is the solution.

This is the way I structure the procedure calls. I have one startup stored procedure responsible for making sure the SQL Server Agent service is running. Then the startup stored procedure creates or re-creates the job that will run the parameter check stored procedure. Then the control goes to the SQLAgent to run the sp__OptionMonitor procedure by having the startup procedure start the newly created job.

The sp__OptionMonitor procedure is the one that does all the checks and re-settings. You will be able to add or to comment out all the pieces that you want. The most complicated part is the one taking care of the memory to allocated to SQL Server. I wanted to have a way to split memory between different SQL Server instances if any. As well, I wanted to be able to give some memory to particular applications when they were installed on the same server machine than is the SQL Server. This last part will need you to configure a bit to have it find what you want. Some settings are going to be applied only once the the service is restarted. No choice. So again your first question will be to ask when the SQL Server service was restarted last. Or find out in the error log files. You have some examples here. You can now apply this to your particular needs. Do not use this code without perfectly understanding its effects.

USE master
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp__OptionMonitor]') 
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp__OptionMonitor]
GO
CREATE PROC dbo.sp__OptionMonitor 
AS
/********************************************************************************************************
** Creation Date: 2002-10-02
** Midif. Date: 2003-01-31
** Created By: avigneau
** Database: master
** Description  : To configure at startup SQL Server parameters  
  
** Parameters   : none 
** Compatibility: SQL Server 7.0, 2000
** Remark: Mod was applied to log whenever an automatic change is applied and to add checking on db chaining
  new 2000 SP3 parameter.
** Example: EXEC sp__OptionMonitor
******************************************************************************************************/SET NOCOUNT ON
DECLARE @iError int, @vcRegValue varchar(255)
,@vcKey varchar(512), @iOptionSetting int
, @iConfigValue int, @iNeededValue int
, @tiInstNodeServer tinyint, @tiInstSQLServer tinyint
DECLARE @vcCommand varchar(2048)
SELECT @tiInstNodeServer = 0,@iNeededValue = 0
-- DB & OS Version control START
DECLARE @SQLVersion varchar(10), @OSVersion dec(4,2)
CREATE TABLE #Version (IndexId int NOT NULL
,Name varchar(60)
,Internal_Value int NULL
,Character_Value varchar(255) NULL)
INSERT #Version EXEC master.dbo.xp_msver
SELECT @SQLVersion = CONVERT(varchar(10),CONVERT(int,CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))*10))
FROM #Version
WHERE Name = 'ProductVersion'
SELECT @OSVersion = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))
FROM #Version
WHERE Name = 'WindowsVersion'
DROP TABLE #Version
-- DB & OS Version control END
CREATE TABLE #ConfigValue
(
name varchar(64)
,minimum int
,maximum int
,config_value int
,run_value int
)
INSERT #ConfigValue EXEC sp_configure
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'show advanced options' AND run_value = 1) = 0
BEGIN
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
DELETE #ConfigValue
INSERT #ConfigValue EXEC master.dbo.sp_configure
END
--*******************************************************************************************************
--******************************* max server memory option setup ****************************************
--*******************************************************************************************************
CREATE TABLE #INIFILE(Content varchar(8000))
---///////////////////////////////////////////////////////////////////////////////////////////////
--This following part need you to configure the parameter you want to look for or put in comments.
-- example to get information from a file where its location is stored in the registry
CREATE TABLE #KeyExist (KeyExist int)
SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\A company name\A product name 1'
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
BEGIN
EXEC @iError = master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey,@value_name='CurrentVersion'
               ,@value=@vcRegValue OUTPUT
SELECT @vcKey = @vcKey+'\'+@vcRegValue
SELECT @vcRegValue = NULL
EXEC @iError = master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey,@value_name='InstallPath'
               ,@value=@vcRegValue OUTPUT
SELECT @vcRegValue = REPLACE('TYPE "'+@vcRegValue+'\Bin\INIFile.ini"','\\','\')
IF @vcRegValue IS NOT NULL
BEGIN
INSERT #INIFILE EXEC master.dbo.xp_cmdshell @vcRegValue
IF (SELECT COUNT(*) FROM #INIFILE WHERE Content LIKE '%text to find as an indication%') <> 0
SELECT @tiInstNodeServer = @tiInstNodeServer+1
END
END
DELETE #KeyExist
-- Example to get the information you need directly from the registry for a value contents
SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\A company name\A product name 2' 
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
BEGIN
EXEC @iError = master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
,@value_name='CurrentVersion'
               ,@value=@vcRegValue OUTPUT
IF convert(float,@vcRegValue) > 3.2
SELECT @tiInstNodeServer = @tiInstNodeServer+1
END
DELETE #KeyExist
-- Example to get the information you need directly from the registry for a leaf name
SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\A company name\A product name 3' 
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
BEGIN
SELECT @tiInstNodeServer = @tiInstNodeServer+1
END
DELETE #KeyExist
--This following part need you to configure the parameter you want to look for or put in comments.
---///////////////////////////////////////////////////////////////////////////////////////////////
-- Get number of installed SQL Server instances
CREATE TABLE #Instances (Value varchar(128), InstanceName varchar(128))
SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\Microsoft\Microsoft SQL Server'
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
INSERT #Instances EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
                  ,@value_name='InstalledInstances',@value=@vcRegValue OUTPUT
SELECT @tiInstSQLServer = count(*) from #Instances
IF @tiInstSQLServer = 0
SELECT @tiInstSQLServer = 1
-- get available physical memory
CREATE TABLE #OptionSetting
(
IndexValue int
,Name varchar(64)
,Internal_Value int
,Character_Value varchar(128)
)                                                                                                                                                                                                    
INSERT #OptionSetting exec master.dbo.xp_msver PhysicalMemory
SELECT @iOptionSetting = Internal_Value FROM #OptionSetting

-- Get the SQL Server Memory Config value
SELECT @iConfigValue = config_value FROM #ConfigValue WHERE name = 'max server memory (MB)'
-- Calculate needed memory for other programs to limit memory taken by SQL Server
IF @tiInstNodeServer <> 0
BEGIN
SELECT @iNeededValue = (@iOptionSetting -(@iOptionSetting*(((@iOptionSetting*((128.000000+@iOptionSetting) / 
                        @iOptionSetting))-@iOptionSetting)/(@iOptionSetting*((128.000000+@iOptionSetting) / 
                        @iOptionSetting)))*@tiInstNodeServer*(@tiInstNodeServer+1)/@tiInstNodeServer*.75)) /
                        @tiInstSQLServer
IF @iNeededValue < 16
SELECT @iNeededValue = 16
END
ELSE
SELECT @iNeededValue = @iOptionSetting/@tiInstSQLServer
-- Set the max memory value accordingly
IF @iConfigValue <> @iNeededValue
BEGIN
IF @iNeededValue = @iOptionSetting OR (@tiInstNodeServer = 0 AND @tiInstSQLServer = 1)
BEGIN
IF @iConfigValue <> 0
BEGIN
EXEC master.dbo.sp_configure 'max server memory (MB)', 0
EXEC master.dbo.xp_logevent 100000
, 'The SQL Server parameter ''max server memory (MB)'' you earlier set will be reverted to its 
default value on next SQL Server Service restart. To avoid this behavior see your system administrator.' 
, 'WARNING'
END
END
ELSE
BEGIN
EXEC master.dbo.sp_configure 'max server memory (MB)', @iNeededValue
EXEC master.dbo.xp_logevent 100000
, 'The SQL Server parameter ''max server memory (MB)'' you earlier set will be reverted to its 
maximum value considering locally installed service needs on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
RECONFIGURE WITH OVERRIDE
END
--***********************************************************************************************************
--*********************************** Other server settings options  ****************************************
--***********************************************************************************************************
-- reset all others to the needed defaults
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'affinity mask' AND run_value = 0) = 0
BEGIN
EXEC master.dbo.sp_configure 'affinity mask',0
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''affinity mask'' you earlier set will be 
reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'allow updates' AND run_value = 0) = 0
BEGIN
EXEC master.dbo.sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''allow updates'' you earlier set will be 
reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) 
FROM #ConfigValue
WHERE name = 'Cross DB Ownership Chaining' 
AND run_value = 0) = 0 
AND (SELECT COUNT(*) 
FROM #ConfigValue 
WHERE name = 'Cross DB Ownership Chaining') = 1
BEGIN
EXEC master.dbo.sp_configure 'Cross DB Ownership Chaining',0
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''Cross DB Ownership Chaining'' you earlier 
set will be reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'locks' AND run_value = 0) = 0
BEGIN
EXEC master.dbo.sp_configure 'locks',0
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''locks'' you earlier set will be reverted 
to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'max worker threads' AND run_value = 255) = 0
BEGIN
EXEC master.dbo.sp_configure 'max worker threads', 255
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''max worker threads'' you earlier set will be 
reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'min memory per query (KB)' AND run_value = 1024) = 0
BEGIN
EXEC master.dbo.sp_configure 'min memory per query (KB)',1024
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''min memory per query (KB)'' you earlier set 
will be reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'nested triggers' AND run_value = 1) = 0
BEGIN
EXEC master.dbo.sp_configure 'nested triggers',1
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''nested triggers'' you earlier set will be 
reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'open objects' AND run_value = 0) = 0
BEGIN
EXEC master.dbo.sp_configure 'open objects',0
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''open objects'' you earlier set will be 
reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'user connections' AND run_value = 0) = 0
BEGIN
EXEC master.dbo.sp_configure 'user connections',0
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''user connections'' you earlier set will be 
reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'user options' AND run_value = 0) = 0
BEGIN
EXEC master.dbo.sp_configure 'user options',0
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''user options'' you earlier set will be 
reverted to its default value on next SQL Server Service restart. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
--***********************************************************************************************************
--*********************************** max degree of parallelism option setup ********************************
--***********************************************************************************************************
-- Until the BUG on parallelism is tested with SQL versions and positive applications
DELETE #OptionSetting
INSERT #OptionSetting exec master.dbo.xp_msver ProcessorCount
SELECT @iOptionSetting = Internal_Value FROM #OptionSetting
SELECT @iConfigValue = config_value FROM #ConfigValue WHERE name = 'max degree of parallelism'
IF @iOptionSetting > 1 AND @iConfigValue <> 1
BEGIN
EXEC master.dbo.sp_configure 'max degree of parallelism',1
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''max degree of parallelism'' was set to ''1'' 
since some queries still does not perform well under multiple processors system. 
To avoid this behavior see your system administrator.' , 'WARNING'
END
DROP TABLE #ConfigValue
--******************************************************************************************************
--********************************** Database option control setup *************************************
--******************************************************************************************************
CREATE TABLE #dboption(OptionName varchar(128))
DECLARE crDB CURSOR
READ_ONLY
FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','tempdb')
DECLARE @DBName varchar(40)
OPEN crDB
FETCH NEXT FROM crDB INTO @DBName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @vcCommand = 'OSQL -S '+@@SERVERNAME+' -E -d' + @DBName + 
       ' -n -w8000 -Q "EXEC master.dbo.sp_dbcmptlevel '+@DBName+', '+@SQLVersion+'"'
EXEC master.dbo.xp_cmdshell @vcCommand, NO_OUTPUT
INSERT #dboption EXEC master.dbo.sp_dboption @DBName
-- not needed system wide. if needed set withing the applications
--DELETE #dboption WHERE OptionName = 'select into/bulkcopy'
--IF @@ROWCOUNT <> 1
--EXEC master.dbo.sp_dboption @DBName,'select into/bulkcopy',true
DELETE #dboption WHERE OptionName = 'trunc. log on chkpt.'
IF @@ROWCOUNT <> 1
BEGIN
SELECT @vcCommand = 'The database parameter ''trunc. log on chkpt'' was set to ''true'' automatically 
for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
EXEC master.dbo.sp_dboption @DBName,'trunc. log on chkpt.',true
EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
END
DELETE #dboption WHERE OptionName = 'auto create statistics'
IF @@ROWCOUNT <> 1
BEGIN
SELECT @vcCommand = 'The database parameter ''auto create statistics'' was set to ''true'' 
automatically for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
EXEC master.dbo.sp_dboption @DBName,'auto create statistics',true
EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
END
DELETE #dboption WHERE OptionName = 'auto update statistics'
IF @@ROWCOUNT <> 1
BEGIN
SELECT @vcCommand = 'The database parameter ''auto update statistics'' was set to ''true'' 
automatically for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
EXEC master.dbo.sp_dboption @DBName,'auto update statistics',true
EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
END
DELETE #dboption WHERE OptionName = 'torn page detection'
IF @@ROWCOUNT <> 1
BEGIN
SELECT @vcCommand = 'The database parameter ''torn page detection'' was set to ''true'' 
automatically for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
EXEC master.dbo.sp_dboption @DBName,'torn page detection',true
EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
END
DELETE #dboption 
WHERE OptionName in ('subscribed','published','merge puplished','dbo use only','offline','single user')
IF EXISTS (SELECT * FROM #dboption)
BEGIN
UPDATE #dboption SET OptionName = 'EXEC master.dbo.sp_dboption '+@DBName+','''+OptionName+''',false'
WHILE EXISTS (SELECT * FROM #dboption)
BEGIN
SET ROWCOUNT 1
SELECT @vcCommand = OptionName FROM #dboption
SET ROWCOUNT 0
IF @vcCommand = 'EXEC master.dbo.sp_dboption msdb,''db chaining'',false'
BEGIN
DELETE #dboption WHERE OptionName = @vcCommand
CONTINUE
END
EXEC (@vcCommand)
DELETE #dboption WHERE OptionName = @vcCommand
SELECT @vcCommand = 'The database parameter '
+SUBSTRING(SUBSTRING(@vcCommand,PATINDEX('%,%',@vcCommand)+1,LEN(@vcCommand) - 
PATINDEX('%,%',@vcCommand)),1
,PATINDEX('%,%',SUBSTRING(@vcCommand,PATINDEX('%,%',@vcCommand)+1,LEN(@vcCommand) - 
PATINDEX('%,%',@vcCommand)))-1)
+' was set to ''false'' automatically for database '''+@DBName+'''. 
To avoid this behavior see your system administrator.'
EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
END
END
END
FETCH NEXT FROM crDB INTO @DBName
END
CLOSE crDB
DEALLOCATE crDB
--*****************************************************************************************************
--************************************************** END **********************************************
--*****************************************************************************************************
GO

Because many programs I have written depend on the SQL Agent Service and to really make sure that

no one has configured this service to run manually (you see many things in life) I rely on this

little piece of code

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__CheckSQLAgent]')

    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[sp__CheckSQLAgent]
GO
CREATE PROC dbo.sp__CheckSQLAgent
AS
/************************************************************************************************
** Creation Date: 2002-10-02
** Created By: avigneau
** Database: master
** Description  : If SQL Agent is not already running run it  
** Parameters   : none (could be flagged for startup)
** Compatibility: SQL Server 7.0, 2000
** Remark: You need the privilege to run xp_cmdshell extended.
** Example: EXEC sp__CheckSQLAgent
****************************************************************************************************/
--****************************************************************************************************
--************************ Make sure SQL Agent Service is running ************************************
--****************************************************************************************************
SET NOCOUNT ON
DECLARE @flag int,@vcCommand varchar(2048)
EXEC @flag = msdb..sp_sqlagent_notify 'G'
IF @flag <> 0
BEGIN
SELECT @vcCommand = 'NET START '+CASE WHEN PATINDEX('%\%',@@SERVERNAME) = 0 THEN 'SQLSERVERAGENT'
ELSE 'SQLAGENT$'+SUBSTRING(@@SERVERNAME,PATINDEX('%\%',@@SERVERNAME)+1,LEN(@@SERVERNAME) -
PATINDEX('%\%',@@SERVERNAME))END
EXEC master.dbo.xp_cmdshell @vcCommand
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[sp__ParamControl]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp__ParamControl]
GO
CREATE PROC dbo.sp__ParamControl
AS
/****************************************************************************************************
** Creation Date: 2002-10-02
** Created By: avigneau
** Database: master
** Description  : To configure at startup SQL Server parameters  
** Parameters   : none (flagged for startup)
** Compatibility: SQL Server 7.0, 2000
** Remark: 
** Example: EXEC sp__ParamControl
******************************************************************************************************/
--*****************************************************************************************************
--************************ Make sure SQL Agent Service is running *************************************
--*****************************************************************************************************
SET NOCOUNT ON
EXEC dbo.sp__CheckSQLAgent
--*****************************************************************************************************
--************************ Refresh the JOB (Just in case someome modofied it) *************************
--*****************************************************************************************************
BEGIN TRANSACTION
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'SQL Server Monitoring') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'SQL Server Monitoring'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id
  FROM   msdb.dbo.sysjobs
  WHERE (name = N'Parameter Control')
  IF (@JobID IS NOT NULL)
  BEGIN
  -- Check if the job is a multi-server job
  IF (EXISTS (SELECT  *
              FROM    msdb.dbo.sysjobservers
              WHERE   (job_id = @JobID) AND (server_id <> 0)))
  BEGIN
    -- There is, so abort the script
    RAISERROR
   (N'Unable to import job ''Parameter Control'' since there is already a multi-server job with this name.'
, 16, 1)
    GOTO QuitWithRollback
  END
  ELSE
    -- Delete the [local] job
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Parameter Control'
    SELECT @JobID = NULL
  END
BEGIN
  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT
      , @job_name = N'Parameter Control', @owner_login_name = N'sa'
, @description = N'No description available.'
   , @category_name = N'SQL Server Monitoring', @enabled = 1, @notify_level_email = 0
, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1
     , @step_name = N'Step 1', @command = N'EXEC master.dbo.sp__OptionMonitor', @database_name = N'master'
  , @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0
  , @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N''
  , @on_success_step_id = 0 , @on_success_action = 1, @on_fail_step_id = 0
  , @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    -- Add the job schedules
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1'
   , @enabled = 1, @freq_type = 4, @active_start_date = 20021018, @active_start_time = 60000
   , @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0
, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    -- Add the Target Servers
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO   EndSave
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: 
--*******************************************************************************************************
--**************************      Start right away this JOB         *************************************
--*******************************************************************************************************
EXEC msdb.dbo.sp_start_job @job_id = @JobID
--********************************************************************************************************
--********************************              END                       ********************************
--********************************************************************************************************
GO
EXEC master.dbo.sp_procoption @ProcName = 'sp__ParamControl', @OptionName = 'startup', @OptionValue = 'TRUE'
GO
EXEC master.dbo.sp__ParamControl
GO