-
Notifications
You must be signed in to change notification settings - Fork 52
/
Change the SQL Agent job history purge period from 30 days to 90 days in the Ola Hallengren sp_purge_jobhistory job.sql
89 lines (74 loc) · 2.42 KB
/
Change the SQL Agent job history purge period from 30 days to 90 days in the Ola Hallengren sp_purge_jobhistory job.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- Change the SQL Agent job history purge period from 30 days to 90 days in the Ola Hallengren sp_purge_jobhistory job
-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox
-- This script locates the 'sp_purge_jobhistory' job and edits it to change the default job history purge period from 30 days to 90 days instead.
SET NOCOUNT ON;
DECLARE @print_only BIT = 1,
@job_id UNIQUEIDENTIFIER,
@name sysname,
@command NVARCHAR(MAX),
@message VARCHAR(1000);
DECLARE @test TABLE
(
[step_id] INT NULL,
[step_name] sysname NULL,
[subsystem] NVARCHAR(40) NULL,
[command] NVARCHAR(MAX) NULL,
[flags] INT NULL,
[cmdexec_success_code] INT NULL,
[on_success_action] TINYINT NULL,
[on_success_step_id] INT NULL,
[on_fail_action] TINYINT NULL,
[on_fail_step_id] INT NULL,
[server] sysname NULL,
[database_name] sysname NULL,
[database_user_name] sysname NULL,
[retry_attempts] INT NULL,
[retry_interval] INT NULL,
[os_run_priority] INT NULL,
[output_file_name] NVARCHAR(200) NULL,
[last_run_outcome] INT NULL,
[last_run_duration] INT NULL,
[last_run_retries] INT NULL,
[last_run_date] INT NULL,
[last_run_time] INT NULL,
[proxy_id] INT NULL
);
DECLARE [job_cur] CURSOR LOCAL FAST_FORWARD FOR
SELECT [job_id],
[name]
FROM [msdb].[dbo].[sysjobs]
WHERE [name] = 'sp_purge_jobhistory'
FOR READ ONLY;
OPEN [job_cur];
FETCH [job_cur]
INTO @job_id,
@name;
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @test;
INSERT INTO @test
EXEC [msdb].[dbo].[sp_help_jobstep] @job_id = @job_id, @step_id = 1;
SELECT @command = [command]
FROM @test;
IF CHARINDEX(N'DATEADD(dd,-30,GETDATE())', @command) > 0
BEGIN
SET @command = REPLACE(@command, N'DATEADD(dd,-30,GETDATE())', N'DATEADD(dd,-90,GETDATE())');
IF @print_only = 0
BEGIN
EXEC [msdb].[dbo].[sp_update_jobstep] @job_id = @job_id,
@step_id = 1,
@command = @command;
SET @message = @name + ': updated command to ' + @command;
END;
ELSE
BEGIN
SET @message = @name + ': would update command to ' + @command;
END;
PRINT @message;
END;
FETCH [job_cur]
INTO @job_id,
@name;
END;
CLOSE [job_cur];
DEALLOCATE [job_cur];