<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Textbox Name="Textbox2">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>="Required Jobs Have been created on " &amp; First(Fields!ServerName.Value, "ServerName")</Value>
                <Style>
                  <FontSize>26pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox2</rd:DefaultName>
        <Top>0.22916in</Top>
        <Left>0.4125in</Left>
        <Height>2.4in</Height>
        <Width>6.725in</Width>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <VerticalAlign>Middle</VerticalAlign>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Textbox Name="Textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Back To Deployment Page</Value>
                <Style>
                  <FontSize>22pt</FontSize>
                  <Color>DarkBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox1</rd:DefaultName>
        <ActionInfo>
          <Actions>
            <Action>
              <Drillthrough>
                <ReportName>MonitorDeployAdminMonitor</ReportName>
              </Drillthrough>
            </Action>
          </Actions>
        </ActionInfo>
        <Top>2.85833in</Top>
        <Left>0.4125in</Left>
        <Height>0.59167in</Height>
        <Width>6.725in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <BackgroundColor>LightSteelBlue</BackgroundColor>
          <VerticalAlign>Middle</VerticalAlign>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Textbox Name="Textbox3">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>The next step in the deployment process will create a folder named</Value>
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value />
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>"C:\ExtendedEvents"  </Value>
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value />
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>This folder will be used to create the Extended Events sessions.  I will also be used by the jobs that read the data from the session files.</Value>
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value />
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>If this is not the path you wish to use, please update the Config in the AdminMonitor database with the path you want to use.  Update the row that has a ConfigTile of "ExtendedEventFolder".  You MUST do this prior to the next step.</Value>
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value />
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>In addition, the next requires that the XP_cmdshell to be enabled.  If you do not wish to do this, you can manually create the folder, but you must update Config table, which is mentioned above.</Value>
                <Style>
                  <FontSize>14pt</FontSize>
                  <Color>White</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style>
              <TextAlign>Center</TextAlign>
            </Style>
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>Textbox3</rd:DefaultName>
        <Top>3.705in</Top>
        <Left>0.39667in</Left>
        <Height>3.15833in</Height>
        <Width>6.74083in</Width>
        <ZIndex>2</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
    </ReportItems>
    <Height>7.70833in</Height>
    <Style>
      <Border>
        <Style>None</Style>
      </Border>
      <BackgroundColor>DarkBlue</BackgroundColor>
    </Style>
  </Body>
  <Width>7.64167in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="Local">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=LocalHost;Initial Catalog=model</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>13fd6a3a-07c6-415e-870e-f09bf951b25b</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="CreatePerfJob">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]


/****** Object:  Job [CollectPerfCounters]    Script Date: 3/23/2023 9:22:25 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 3/23/2023 9:22:25 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorCollectPerfCounters')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorCollectPerfCounters', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [Collect]    Script Date: 3/23/2023 9:22:25 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO AdminMonitor.[dbo].[PerfCountersCaptured]

SELECT p.CounterID
	, d.Cntr_value
	, d.cntr_type
	, GETDATE()
FROM sys.dm_os_performance_counters d
	INNER JOIN AdminMonitor.[dbo].[PerformanceCountersToCapture] p
		ON d.object_name = p.objectName
			AND d.Counter_name = p.countername
				AND d.instance_name = p.instancename
WHERE p.active = 1', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every10Sec', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=2, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230226, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="DeleteJob">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]

/****** Object:  Job [MonitorPurgeInformation]    Script Date: 8/27/2023 7:50:48 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorPurgeInformation')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorPurgeInformation', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'This job purges old data.  The amount of data to retain is based on the DataRetention setting in the Config table.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [Purge Wait Data]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Wait Data', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[WaitCaptured]
WHERE [DateTimeOfCapture] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Purge Perf Data]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 2)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Perf Data', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].PerfCountersCaptured
WHERE [DateTimeOfCapture] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Purge Deadlock Count]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 3)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Deadlock Count', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[DeadlockCount]
WHERE [DateOfCheck] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Purge Deadlock Detail]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 4)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge Deadlock Detail', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[DeadlockDetail]
WHERE [LocalTimeOfDeadLock] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Purge WIndows Counters]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 5)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge WIndows Counters', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[WindowsCountersCaptured]
WHERE [DateTimeOfCapture] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Purge BlockingCount]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 6)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge BlockingCount', 
		@step_id=6, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.dbo.BlockingCount
WHERE DateOfCheck &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Purge BlockingDetail]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 7)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge BlockingDetail', 
		@step_id=7, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.dbo.BlockingDetail
WHERE LocalTimeOfBlocking &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [PurgeServerActivity]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 8)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PurgeServerActivity', 
		@step_id=8, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.dbo.[ServerActivity]
WHERE [DateTimeOfCapture] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [PurgeDeletedDatabaseUsers]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 9)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PurgeDeletedDatabaseUsers', 
		@step_id=9, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[DroppedDatabaseUsers]
WHERE [DateTimeOfDelete] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [PurgeQueryTimeout]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 10)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PurgeQueryTimeout', 
		@step_id=10, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[QueryTimeout]
WHERE [LocalTimeOfTimeout] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [PurgeDatabaseSize]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 11)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PurgeDatabaseSize', 
		@step_id=11, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[DatabaseSize]
WHERE [DateOfCapture] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [PurgeDeletedLogins]    Script Date: 8/27/2023 7:50:48 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 12)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PurgeDeletedLogins', 
		@step_id=12, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
DECLARE @DaysToRetain INT

SELECT @DaysToRetain = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE [ConfigTitle] = ''DataRetention''

SET @DaysToRetain = @DaysToRetain * -1



DELETE AdminMonitor.[dbo].[DroppedLogins]
WHERE [TimeOfDelete] &lt; DateADD(day, @DaysToRetain, GetDate())', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 3 AM', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230221, 
		@active_end_date=99991231, 
		@active_start_time=30000, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:
</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="CollectWaits">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]


/****** Object:  Job [WaitTypeCapture]    Script Date: 3/23/2023 10:14:01 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 3/23/2023 10:14:01 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorWaitTypeCapture')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorWaitTypeCapture', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [Capture]    Script Date: 3/23/2023 10:14:02 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Capture', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO [dbo].[WaitCaptured]
(WaitTypeID, TotalWaitTimeSec, ResourceWaitTimeSec,
		SignalWaitTimeSec, WaitCount, DateTimeOfCapture)



SELECT  t.WaitTypeID
, 
                s.wait_time_ms/1000.0
AS [Total Wait Time (second)], 
                (s.wait_time_ms-s.signal_wait_time_ms)/1000.0
AS [Resource Wait Time (second)], 
                s.signal_wait_time_ms/1000.0
AS [Signal Wait Time (second)], 
                s.waiting_tasks_count
AS [Wait Count]
, GETDATE()
  FROM sys.dm_os_wait_stats s
	INNER JOIN WaitTypesToCollect t
	 ON s.wait_Type = t.WaitTypeName
WHERE s.wait_type IN (
SELECT WaitTypeName FROM  [dbo].[WaitTypesToCollect] where active = 1)', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Capture', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=2, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230219, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="CollectReadWriteLatency">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]

/****** Object:  Job [CollectReadWriteLatency]    Script Date: 3/31/2023 6:11:02 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 3/31/2023 6:11:02 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'CMonitorollectReadWriteLatency')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorCollectReadWriteLatency', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CollectLatency]    Script Date: 3/31/2023 6:11:03 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CollectLatency', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

INSERT INTO ReadWriteLatency
SELECT 	
		DB_NAME(v.database_id) AS ''DatabaseName'',
		Getdate(),
		
	CASE WHEN RIGHT(m.physical_name,3) = ''MDF'' THEN ''DataFile''
		 WHEN RIGHT(m.physical_name,3) = ''NDF'' THEN ''DataFile''
		 WHEN RIGHT(m.physical_name,3) = ''LDF'' THEN ''LogFile''
		 ELSE ''UnknownFileType'' END AS ''FileType'',
		LEFT(m.physical_name,2) AS ''Drive'',
	CASE 
		WHEN num_of_reads = 0 
			THEN 0 
		ELSE (io_stall_read_ms/num_of_reads) 
	END AS ''ReadLatency'',
	CASE 
		WHEN io_stall_write_ms = 0 
			THEN 0 
		ELSE (io_stall_write_ms/num_of_writes) 
	END AS ''WriteLatency'',
	num_of_reads,
	num_of_bytes_read,
	num_of_bytes_written,
	num_of_writes,
	m.physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS v
INNER JOIN sys.master_files m
    ON v.database_id = m.database_id 
    AND v.file_id = m.file_id
', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every10Seconds', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=2, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230330, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="ServerName">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>Select @@servername AS 'ServerName'</CommandText>
      </Query>
      <Fields>
        <Field Name="ServerName">
          <DataField>ServerName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="WindowsCounterJob">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]

declare @server nvarchar(100)
SELECT @server = @@servername

declare @jobstep nvarchar(4000)
SET @jobstep = N'

$DateOfCapture = get-date -Format "MM/dd/yyyy HH:mm:ss"
$serverName = ''' + @server +'''
$databaseName = "AdminMonitor"
$tableName = "dbo.WindowsCountersCaptured"
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=''$serverName'';database=''$databaseName'';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

$Counter = Get-Counter -counter "\System\Processor Queue Length"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()

$Counter = Get-Counter -counter  "\PhysicalDisk(_Total)\Avg. Disk sec/Write"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()


$Counter = Get-Counter -counter  "\PhysicalDisk(_Total)\Avg. Disk sec/Read"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()



$Counter = Get-Counter -counter  "\Paging File(_Total)\% Usage"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()


$Counter = Get-Counter -counter  "\Memory\Available MBytes"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()


$Counter = Get-Counter -counter  "\SQLServer:Buffer Manager\buffer cache hit ratio"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()



$Counter = Get-Counter -counter  "\Processor(_Total)\% Processor Time"
$CV = $Counter.CounterSamples[0].CookedValue
$counterName = $Counter.CounterSamples[0].Path
  $insertquery="
  INSERT INTO $tableName
      (WindowsCounterName,CounterValue, DateTimeOfCapture)
    VALUES
      (''$counterName'',''$CV'', ''$DateOfCapture'')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()

$Connection.Close();'


/****** Object:  Job [MonitorWindowsPerfCounters]    Script Date: 5/11/2023 7:18:55 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorWindowsPerfCounters')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorWindowsPerfCounters', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'This job captures the Windows counters using Powershell.  Found that having them all in one step helps manage the CPU utilization.  If there are different steps the CPU utilization will jump 3 to 5 percent.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureCounters]    Script Date: 5/11/2023 7:18:55 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureCounters', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=@jobstep, 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every30Sec', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230401, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="DeadlockCapture">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]






DECLARE @EEPath NVARCHAR(100)


SELECT @EEPath = configvalue + '\Deadlock*.xel'
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = 'ExtendedEventFolder'
Declare @Jobstepcode NVARCHAR(3000)

SET @jobstepcode = N'SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

USE [AdminMonitor]



SELECT event_data = convert(xml, event_data) 
INTO #eeTable 
 FROM sys.fn_xe_file_target_read_file(N''' + @EEPath + ''', null, null, null); 

SELECT 
  LocalTimeOfEvent		= DATEADD(MI, (DATEDIFF(MI, GETUTCDATE(), GETDATE())), event_data.value(N''(event/@timestamp)[1]'', N''datetime'')),
  UTCTimeOfEvent =  event_data.value(N''(event/@timestamp)[1]'', N''datetime''),
  DatabaseName  = event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'')
 FROM #eeTable
WHERE  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DateAdd(minute, -5,getutcdate())
AND 
(event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'') &lt;&gt; '' '') 
ORDER BY LocalTimeOfEvent

IF @@rowcount = 0
	BEGIN
				INSERT [dbo].[DeadlockDetail]
(LocalTimeOfDeadlock, UTCTimeOfDeadLock, DatabaseName)
VALUES(Getdate(), GETUTCDATE(), ''No Deadlocks Found'')
	END
ELSE
	BEGIN
		INSERT INTO[dbo].[DeadlockDetail]
(LocalTimeOfDeadlock, UTCTimeOfDeadLock, DatabaseName)
SELECT 
  LocalTimeOfEvent		= DATEADD(MI, (DATEDIFF(MI, GETUTCDATE(), GETDATE())), event_data.value(N''(event/@timestamp)[1]'', N''datetime'')),
  UTCTimeOfEvent =  event_data.value(N''(event/@timestamp)[1]'', N''datetime''),
  DatabaseName  = event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'')
 FROM #eeTable
WHERE  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DateAdd(minute, -5,getutcdate())
AND 
(event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'') &lt;&gt; '' '') 
ORDER BY LocalTimeOfEvent
	END

DROP TABLE #eeTable
'
DECLARE @jobstep2 NVARCHAR(1000)

SET @jobstep2 = N'SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

USE [AdminMonitor]

SELECT event_data = convert(xml, event_data) 
INTO #eeTable 
 FROM sys.fn_xe_file_target_read_file(N''' + @EEPath + ''', null, null, null); 
 INSERT INTO [dbo].[DeadlockCount]
 (DateOfCheck, NumberOfEvents)
SELECT 
 GetDate(),
  ts		= COUNT(event_data.value(N''(event/@timestamp)[1]'', N''datetime''))
 
FROM #eeTable
Where  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DateAdd(minute, -5,getutcdate())
and 
(event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'') &lt;&gt; '' '') 
ORDER BY ts


DROP TABLE #eeTable'

/****** Object:  Job [MonitorCaptureDeadlocks]    Script Date: 4/9/2023 8:31:51 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorCaptureDeadlocks')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorCaptureDeadlocks', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'This job requires the Deadlocks Extended Event Session.  Code is below  CREATE EVENT SESSION [Deadlocks] ON SERVER 
ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
ADD EVENT sqlserver.database_xml_deadlock_report,
ADD EVENT sqlserver.lock_deadlock,
ADD EVENT sqlserver.lock_deadlock_chain(
    ACTION(sqlserver.database_name,sqlserver.nt_username)),
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N''C:\ExtendedEv', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [EventDetail]    Script Date: 4/9/2023 8:31:52 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'EventDetail', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=@Jobstepcode, 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [DeadLock]    Script Date: 4/9/2023 8:31:52 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 2)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DeadLock', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=@jobstep2, 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every30Mon', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230408, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="BlockingCapture">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]






DECLARE @EEPath NVARCHAR(100)


SELECT @EEPath = configvalue + '\BlockedProcess*.xel'
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = 'ExtendedEventFolder'
Declare @Jobstepcode NVARCHAR(3000)

SET @jobstepcode =N'SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

USE [AdminMonitor]

DECLARE @EEPath NVARCHAR(100)
SELECT @EEPath = [ConfigValue] + ''\blockedProcess*.xel''
  FROM [AdminMonitor].[dbo].[Config]
  where ConfigTitle = ''ExtendedEventFolder''

SELECT event_data = convert(xml, event_data) 
INTO #eeTable 
 FROM sys.fn_xe_file_target_read_file(''' + @EEPath + ''', null, null, null); 
 INSERT INTO AdminMonitor.[dbo].[BlockingCount]
 (DateOfCheck, NumberOfEvents)

SELECT 
 GetDate(),
  COUNT(event_data.value(N''(event/@timestamp)[1]'', N''datetime''))
FROM #eeTable
Where  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DateAdd(minute, -5,getutcdate())
and 
(event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'') &lt;&gt; '' '') 


-------
SELECT 
  LocalTimeOfEvent		= DATEADD(MI, (DATEDIFF(MI, GETUTCDATE(), GETDATE())), event_data.value(N''(event/@timestamp)[1]'', N''datetime'')),
  UTCTimeOfEvent =  event_data.value(N''(event/@timestamp)[1]'', N''datetime''),
  DatabaseName  = event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'')
 FROM #eeTable
WHERE  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DateAdd(minute, -5,getutcdate())
AND 
(event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'') &lt;&gt; '' '') 
ORDER BY LocalTimeOfEvent


IF @@rowcount = 0
	BEGIN
INSERT AdminMonitor.[dbo].[BlockingDetail]
(LocalTimeOfBlocking, UTCTimeOfBlocking, DatabaseName)
VALUES(Getdate(), GETUTCDATE(), ''No Blocks Found'')
	END
ELSE
	BEGIN
		INSERT INTO AdminMonitor.[dbo].[BlockingDetail]
(LocalTimeOfBlocking, UTCTimeOfBlocking, DatabaseName)
SELECT 
  DATEADD(MI, (DATEDIFF(MI, GETUTCDATE(), GETDATE())), event_data.value(N''(event/@timestamp)[1]'', N''datetime'')),
  event_data.value(N''(event/@timestamp)[1]'', N''datetime''),
  event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'')
 FROM #eeTable
WHERE  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DateAdd(minute, -5,getutcdate())
AND 
(event_data.value(N''(event/data[@name="database_name"]/value)[1]'', N''nvarchar(max)'') &lt;&gt; '' '') 

	END'
/****** Object:  Job [MonitorCaptureBlocking]    Script Date: 4/23/2023 8:16:26 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorCaptureBlocking')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorCaptureBlocking', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureBlocking]    Script Date: 4/23/2023 8:16:27 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureBlocking', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=@jobstepcode, 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every5Minutes', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230423, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="BaselineCalculation">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]


/****** Object:  Job [MonitorCalculateBaseline]    Script Date: 5/9/2023 7:45:00 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 5/9/2023 7:45:00 PM ******/


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorCalculateBaseline')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorCalculateBaseline', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [BlockingBaseline]    Script Date: 5/9/2023 7:45:01 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BlockingBaseline', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @NumDays INT
DECLARE @MinCaptures INT
DECLARE @ActualCaptures INT
DECLARE @BaseLineValue INT

SELECT @NumDays = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = ''NumberOfDaysForBaseline''

SELECT @MinCaptures = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = ''MinCapturesBaseline''

SET @Numdays = @Numdays * -1

Select @ActualCaptures = COUNT([NumberOfEvents])
FROM AdminMonitor.[dbo].[BlockingCount]
WHERE [DateOfCheck] &gt; DATEADD(day, @NumDays, GETDATE())

IF @ActualCaptures &lt; @MinCaptures
	BEGIN
		UPDATE AdminMonitor.[dbo].[Baseline]
			SET [BaselineValue] = 0
				,[DateOfBaseLine] = Getdate()
				, [NumberOfCapturesUse] = @ActualCaptures
			WHERE CounterName = ''BlockingCount''
	END
ELSE
	BEGIN
		SELECT @BaseLineValue = AVG([NumberOfEvents])
		FROM AdminMonitor.[dbo].[BlockingCount]
		WHERE [DateOfCheck] &gt; DATEADD(day, @NumDays, GETDATE())
		UPDATE AdminMonitor.[dbo].[Baseline]
			SET [BaselineValue] = @BaseLineValue
				,[DateOfBaseLine] = Getdate()
				, [NumberOfCapturesUse] = @ActualCaptures
			WHERE CounterName = ''BlockingCount''
	END', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [DeadlockCountBaseline]    Script Date: 5/9/2023 7:45:01 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 2)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DeadlockCountBaseline', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'

DECLARE @NumDays INT
DECLARE @MinCaptures INT
DECLARE @ActualCaptures INT
DECLARE @BaseLineValue INT

SELECT @NumDays = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = ''NumberOfDaysForBaseline''

SELECT @MinCaptures = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = ''Deadlock''

SET @Numdays = @Numdays * -1

Select @ActualCaptures = COUNT([NumberOfEvents])
FROM AdminMonitor.[dbo].DeadlockCount
WHERE [DateOfCheck] &gt; DATEADD(day, @NumDays, GETDATE())

IF @ActualCaptures &lt; @MinCaptures
	BEGIN
		UPDATE AdminMonitor.[dbo].[Baseline]
			SET [BaselineValue] = 0
				,[DateOfBaseLine] = Getdate()
				, [NumberOfCapturesUse] = @ActualCaptures
			WHERE CounterName = ''Deadlock''
	END
ELSE
	BEGIN
		SELECT @BaseLineValue = AVG([NumberOfEvents])
		FROM AdminMonitor.[dbo].[DeadlockCount]
		WHERE [DateOfCheck] &gt; DATEADD(day, @NumDays, GETDATE())

		UPDATE AdminMonitor.[dbo].[Baseline]
			SET [BaselineValue] = @BaseLineValue
				,[DateOfBaseLine] = Getdate()
				, [NumberOfCapturesUse] = @ActualCaptures
			WHERE CounterName = ''Deadlock''
	END', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monthly', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20230502, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="ServerActivity">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorServerActivity')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorServerActivity', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureActivity]    Script Date: 5/14/2023 7:24:45 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureActivity', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'INSERT INTO AdminMonitor.[dbo].[ServerActivity]
SELECT Count(*) AS ''NumSpids''
, dbid
, hostname
,program_name
, GetDate()
FROM sysprocesses
WHERE spid &gt; 50
GROUP BY dbid
, hostname
,program_name', 
        @database_name=N'AdminMonitor', 
        @flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Hourly', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=8, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20230514, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="PurgeJobHistory">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]


/****** Object:  Job [MonitorPurgeJobHistory]    Script Date: 6/11/2023 10:05:03 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorPurgeJobHistory')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorPurgeJobHistory', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Due to the frequency of the job executions, the job history table can become full and key history might no longer be available.  This job purges job history for the monitoring jobs.  How much history to keep is determined by the JobHistoryRetention setting in the config table.  The default is 3 days.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [PurgeHistory]    Script Date: 6/11/2023 10:05:03 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PurgeHistory', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'USE msdb ;  
GO  

DECLARE @DaysToPurge SMALLINT
DECLARE @OldestDateToKeep DateTime

SELECT @DaysToPurge = [ConfigValue]
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = ''JobHistoryRetention''

SET @DaysToPurge = @DaysToPurge * -1
SET @OldestDateToKeep = DATEADD(Day,@DaysToPurge, GETDATE())

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorCalculateBaseline''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorCaptureBlocking''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorCaptureDeadlocks''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorCollectPerfCounters''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorCollectReadWriteLatency''
, @oldest_date = @oldestDateToKeep 

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorPurgeInformation''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorServerActivity''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorWaitTypeCapture''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorWaitTypeCapture''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorWindowsPerfCounters''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorPurgeJobHistory''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorDeletedLogins''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorDeleteDBUsers''
, @oldest_date = @oldestDateToKeep

EXEC dbo.sp_purge_jobhistory 
@job_name = N''MonitorQueryTimeout''
, @oldest_date = @oldestDateToKeep
GO', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 3 AM', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230523, 
		@active_end_date=99991231, 
		@active_start_time=30000, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="DropLogins">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]






DECLARE @EEPath NVARCHAR(100)


SELECT @EEPath = configvalue + '\DroppedLogin*.xel'
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = 'ExtendedEventFolder'
Declare @Jobstepcode NVARCHAR(3000)

SET @jobstepcode ='SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

USE [AdminMonitor]



SELECT event_data = convert(xml, event_data) 
INTO #eTable 
 FROM sys.fn_xe_file_target_read_file(N''' + @EEPath + ''', null, null, null); 



INSERT INTO AdminMonitor.[dbo].[DroppedLogins]
	([TimeOfDelete], [DeletedLogin], [WhoDeletedIt])
SELECT 
   DATEADD(MI, (DATEDIFF(MI, GETUTCDATE(), GETDATE())), event_data.value(N''(event/@timestamp)[1]'', N''datetime'')),
   LEFT(event_data.value(N''(event/data[@name="statement"]/value)[1]'', N''nvarchar(max)''), 500),
   event_data.value(N''(event/action[@name="username"]/value)[1]'', N''nvarchar(max)'')
FROM #eTable
WHERE  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DATEADD(HOUR, -1,GETDATE())
	AND ((event_data.value(N''(event/data[@name="statement"]/value)[1]'', N''nvarchar(max)'') LIKE ''Drop Login%'')
	AND (event_data.value(N''(event/data[@name="statement"]/value)[1]'', N''nvarchar(max)'') NOT LIKE ''SELECT %''))


DROP TABLE #eTable'


/****** Object:  Job [MonitorDeletedLogins]    Script Date: 6/1/2023 8:38:23 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorDeletedLogins')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorDeletedLogins', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Monitor Job to capture Deleted Logins', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureDeletedLogins]    Script Date: 6/1/2023 8:38:23 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureDeletedLogins', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=@jobstepcode, 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Hourly', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=8, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230531, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="DropDBUser">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]






DECLARE @EEPath NVARCHAR(100)


SELECT @EEPath = configvalue + '\DropDBUser*.xel'
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = 'ExtendedEventFolder'
Declare @Jobstepcode NVARCHAR(3000)

SET @jobstepcode ='SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

USE [AdminMonitor]



SELECT event_data = convert(xml, event_data) 
INTO #eTable 
FROM sys.fn_xe_file_target_read_file(N''' + @EEPath + ''', null, null, null); 

INSERT INTO AdminMonitor.[dbo].[DroppedDatabaseUsers]
	([DateTimeOfDelete], [DroppedUser], [DeletedBy],[DatabaseName]) 
SELECT 
   DATEADD(MI, (DATEDIFF(MI, GETUTCDATE(), GETDATE())), event_data.value(N''(event/@timestamp)[1]'', N''datetime'')),
   LEFT(event_data.value(N''(event/data[@name="statement"]/value)[1]'', N''nvarchar(max)''), 500),
   event_data.value(N''(event/action[@name="username"]/value)[1]'', N''nvarchar(max)''),
   event_data.value(N''(event/action[@name="database_name"]/value)[1]'', N''nvarchar(max)'')
FROM #eTable
WHERE  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DATEADD(HOUR, -1,GETDATE())
	AND ((event_data.value(N''(event/data[@name="statement"]/value)[1]'', N''nvarchar(max)'') LIKE ''%Drop User%'')
	AND (event_data.value(N''(event/data[@name="statement"]/value)[1]'', N''nvarchar(max)'') NOT LIKE ''SELECT %''))
	
DROP TABLE #eTable'


/****** Object:  Job [MonitorDeleteDBUsers]    Script Date: 6/1/2023 8:42:59 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorDeleteDBUsers')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorDeleteDBUsers', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Monitor job to capture deleted Database users', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureDeletedUsers]    Script Date: 6/1/2023 8:43:00 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureDeletedUsers', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=@jobstepcode, 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Hourly', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=8, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230531, 
		@active_end_date=99991231, 
		@active_start_time=3000, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:

</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="DatabaseGrowth">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]


/****** Object:  Job [MonitorDatabaseSize]    Script Date: 6/7/2023 6:43:33 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorDatabaseSize')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorDatabaseSize', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureDBSize]    Script Date: 6/7/2023 6:43:34 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureDBSize', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO AdminMonitor.[dbo].[DatabaseSize]
SELECT 
      DB_NAME(database_id) AS ''DatabaseName''
    , CONVERT(Decimal(10, 2),SUM(CASE WHEN type_desc = ''LOG'' THEN size END) * 8/1024) AS ''LogSize''
    , CONVERT(Decimal(10, 2),SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) * 8/1024) AS ''DataSize''
    , CONVERT(Decimal(10, 2),SUM(size) * 8/1024) AS ''TotalSize''
	, GETDATE() AS ''DateOfCapture''
FROM sys.master_files 
GROUP BY database_id', 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily5AM', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230606, 
		@active_end_date=99991231, 
		@active_start_time=50000, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:
</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="QueryTimeout">
      <Query>
        <DataSourceName>Local</DataSourceName>
        <CommandText>USE [msdb]

DECLARE @EEPath NVARCHAR(100)


SELECT @EEPath = configvalue + '\QueryTimeout*.xel'
FROM AdminMonitor.dbo.Config
WHERE ConfigTitle = 'ExtendedEventFolder'
Declare @Jobstepcode NVARCHAR(3000)

SET @jobstepcode =N'SET QUOTED_IDENTIFIER ON

SELECT event_data = convert(xml, event_data) 
INTO #eeTable 
 FROM sys.fn_xe_file_target_read_file(''' + @EEPath + ''', null, null, null); 


INSERT INTO AdminMonitor.dbo.QueryTimeout

SELECT 
DATEADD(MI, (DATEDIFF(MI, GETUTCDATE(), GETDATE())), event_data.value(N''(event/@timestamp)[1]'', N''datetime'')),
  ts		= event_data.value(N''(event/@timestamp)[1]'', N''datetime''),
  [sql]		= LEFT(event_data.value(N''(event/action[@name="sql_text"]/value)[1]'', N''nvarchar(max)''), 500),
  duration  = event_data.value(N''(event/data[@name="duration"]/value)[1]'', N''nvarchar(max)''),
  
  user_nm	= event_data.value(N''(event/action[@name="username"]/value)[1]'', N''nvarchar(max)''),
  dbname	= event_data.value(N''(event/action[@name="database_name"]/value)[1]'', N''nvarchar(max)'')
  
  
FROM #eeTable
WHERE event_data.value(N''(event/action[@name="sql_text"]/value)[1]'', N''nvarchar(max)'') NOT LIKE ''(@_msparam_0 nvarchar(4000)%''
AND  event_data.value(N''(event/@timestamp)[1]'', N''datetime'') &gt; DateAdd(minute, -5,getutcdate())
AND event_data.value(N''(event/action[@name="database_name"]/value)[1]'', N''nvarchar(max)'') NOT IN (''Master'', ''MSDB'')
ORDER BY ts

IF @@Rowcount = 0
	BEGIN
		INSERT INTO AdminMonitor.dbo.QueryTimeout
			SELECT GETDATE(), GetDate(), ''No Timeouts Found'', 0, ''No User Involved'', ''No TImeouts Found''
	END

DROP TABLE #eeTable'


/****** Object:  Job [MonitorQueryTimeout]    Script Date: 6/11/2023 8:41:29 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0


DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'MonitorQueryTimeout')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MonitorQueryTimeout', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END
/****** Object:  Step [CaptureQueryTimeout]    Script Date: 6/11/2023 8:41:29 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CaptureQueryTimeout', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=@jobstepcode, 
		@database_name=N'AdminMonitor', 
		@flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every5 Min', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20230608, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:</CommandText>
      </Query>
    </DataSet>
  </DataSets>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>1ec26e55-d42e-4c2a-8caa-81b2468f710e</rd:ReportID>
</Report>