February 5, 2017 at 11:23 pm
Hi Experts,
Need some help on tsql logic. I have a small requirement of Archiving msdb job history data into a separate table. In our environment, for some SQL instances, the job history entries are getting cleaned up very fast this is due to lots of sql agent jobs runinng every 15 mins round the clock which is causing a lot of log entries getting generated.
--- created a table which has same structure as msdb jobhishory
use msdb
go
CREATE TABLE [dbo].[sysjobhistory_bkp](
[instance_id] [int] NULL,
[job_id] [uniqueidentifier] NOT NULL,
[step_id] [int] NOT NULL,
[step_name] [sysname] NOT NULL,
[sql_message_id] [int] NOT NULL,
[sql_severity] [int] NOT NULL,
[message] [nvarchar](4000) NULL,
[run_status] [int] NOT NULL,
[run_date] [int] NOT NULL,
[run_time] [int] NOT NULL,
[run_duration] [int] NOT NULL,
[operator_id_emailed] [int] NOT NULL,
[operator_id_netsent] [int] NOT NULL,
[operator_id_paged] [int] NOT NULL,
[retries_attempted] [int] NOT NULL,
[server] [sysname] NOT NULL
)
go
-- current logic for archival. Taking last 45 days worth data and dumping into a the backup table.
use MSDB
GO
DECLARE @oldest_date DATETIME= NULL
DECLARE @datepart INT
SET @oldest_date = GETDATE()-45
--PRINT @oldest_date
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @oldest_date, 112))
--PRINT 'Datepart = '+str(@datepart)
INSERT INTO msdb.dbo.sysjobhistory_bkp
SELECT * FROM msdb.dbo.sysjobhistory
WHERE run_date < @datepart
I have put this inside a SQL Agent Job which runs daily once in midnight.
I have written basic logic but I need help in putting a logic where I should not copy any DUPLICATE records into backup table. How to do so?
Any thoughts please?
Thanks,
Sam
February 6, 2017 at 12:16 am
First thought would be to delete from the sysjobhistory table and use the output clause to archive the entries, no risk of any duplication.
😎
DECLARE @NUM_DAYS INT = 45;
DELETE SJH
OUTPUT
deleted.[instance_id]
,deleted.[job_id]
,deleted.[step_id]
,deleted.[step_name]
,deleted.[sql_message_id]
,deleted.[sql_severity]
,deleted.[message]
,deleted.[run_status]
,deleted.[run_date]
,deleted.[run_time]
,deleted.[run_duration]
,deleted.[operator_id_emailed]
,deleted.[operator_id_netsent]
,deleted.[operator_id_paged]
,deleted.[retries_attempted]
,deleted.[server]
INTO [MyDatabase].[dbo].[sysjobhistory_BACKUP]
FROM [msdb].[dbo].[sysjobhistory] SJH
WHERE SJH.run_date >= ((YEAR(GETDATE()-@NUM_DAYS) * 10000)
+ (MONTH(GETDATE()-@NUM_DAYS) * 100)
+ (DAY(GETDATE()-@NUM_DAYS)))
;
February 6, 2017 at 4:04 am
Sir, at this point I am not looking to delete data from msdb.
February 6, 2017 at 4:20 am
vsamantha35 - Sunday, February 5, 2017 11:23 PMHi Experts,Need some help on tsql logic. I have a small requirement of Archiving msdb job history data into a separate table. In our environment, for some SQL instances, the job history entries are getting cleaned up very fast this is due to lots of sql agent jobs runinng every 15 mins round the clock which is causing a lot of log entries getting generated.
--- created a table which has same structure as msdb jobhishory
use msdb
go
CREATE TABLE [dbo].[sysjobhistory_bkp](
[instance_id] [int] NULL,
[job_id] [uniqueidentifier] NOT NULL,
[step_id] [int] NOT NULL,
[step_name] [sysname] NOT NULL,
[sql_message_id] [int] NOT NULL,
[sql_severity] [int] NOT NULL,
[message] [nvarchar](4000) NULL,
[run_status] [int] NOT NULL,
[run_date] [int] NOT NULL,
[run_time] [int] NOT NULL,
[run_duration] [int] NOT NULL,
[operator_id_emailed] [int] NOT NULL,
[operator_id_netsent] [int] NOT NULL,
[operator_id_paged] [int] NOT NULL,
[retries_attempted] [int] NOT NULL,
[server] [sysname] NOT NULL
)
go-- current logic for archival. Taking last 45 days worth data and dumping into a the backup table.
use MSDB
GO
DECLARE @oldest_date DATETIME= NULL
DECLARE @datepart INTSET @oldest_date = GETDATE()-45
--PRINT @oldest_dateSET @datepart = CONVERT(INT, CONVERT(VARCHAR, @oldest_date, 112))
--PRINT 'Datepart = '+str(@datepart)
INSERT INTO msdb.dbo.sysjobhistory_bkp
SELECT * FROM msdb.dbo.sysjobhistory
WHERE run_date < @datepartI have put this inside a SQL Agent Job which runs daily once in midnight.
I have written basic logic but I need help in putting a logic where I should not copy any DUPLICATE records into backup table. How to do so?Any thoughts please?
Thanks,
Sam
Adding one for condition to identify only new data -------------------------------------------------------------------------------------------------
DECLARE @oldest_date DATETIME = NULL
DECLARE @datepart INT
SET @oldest_date = GETDATE()-45
--PRINT @oldest_date
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @oldest_date, 112))
--PRINT 'Datepart = '+str(@datepart)
INSERT INTO msdb.dbo.sysjobhistory_bkp
SELECT * FROM msdb.dbo.sysjobhistory
WHERE run_date < @datepart
AND instance_id > (SELECT MAX(instance_id) from msdb.dbo.sysjobhistory_bkp) -- NEW DATA CONDITION
-----------------------------------------------------------------------------------------------------------------------------
Regards,
Shankar Walvekar
February 8, 2017 at 8:12 pm
This should do:INSERT INTO msdb.dbo.sysjobhistory_bkp
SELECT * FROM msdb.dbo.sysjobhistory
WHERE run_date < @datepart
AND run_date > (SELECT TOP 1 run_date
FROM msdb.dbo.sysjobhistory_bkp ORDER BY run_date DESC
)
I really hope [run_date ] is the 1st column of a clustered index on both tables.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply