Workplace Encounters: Reclaiming Space from an Oversized Database

Image for post
Image for post

INTRODUCTION

BACKGROUND

EPOEVENTS TABLE

Image for post
Image for post
USE [ePO4_SVR-EPO-02]
GO
EXEC sp_fkeys 'EPOEvents'

PROCEDURE SUMMARY

Summary of Procedure Used During the Change:

Deploy five 200GB Drives

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

Stop EPO Application Services

Backup the EPO Database

BACKUP DATABASE [ePO4_SVR-EPO-02] to
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A01.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A02.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A03.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A04.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A05.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A06.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A07.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A08.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A09.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_A10.bak'
WITH STATS = 5, COMPRESSION, NAME = N'ePO4_SVR-EPO-02 First Backup Before Purge'

Create a New Filegroup

USE [master]
GO
ALTER DATABASE [ePO4_SVR-EPO-02] ADD FILEGROUP [FG_LARGE]
GO
USE [master]
GO
ALTER DATABASE [ePO4_SVR-EPO-02] ADD FILE ( NAME = N'ePO4_SVR-EPO_FG_LARGE_01', FILENAME = N'M:\MSSQL\DATA\ePO4_SVR-EPO_FG_LARGE_01.ndf' , SIZE = 41943040KB , MAXSIZE = 104857600KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [FG_LARGE]
GO
ALTER DATABASE [ePO4_SVR-EPO-02] ADD FILE ( NAME = N'ePO4_SVR-EPO_FG_LARGE_02', FILENAME = N'M:\MSSQL\DATA\ePO4_SVR-EPO_FG_LARGE_02.ndf' , SIZE = 41943040KB , MAXSIZE = 104857600KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [FG_LARGE]
GO
ALTER DATABASE [ePO4_SVR-EPO-02] ADD FILE ( NAME = N'ePO4_SVR-EPO_FG_LARGE_03', FILENAME = N'N:\MSSQL\DATA\ePO4_SVR-EPO_FG_LARGE_03.ndf' , SIZE = 41943040KB , MAXSIZE = 104857600KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [FG_LARGE]
GO
ALTER DATABASE [ePO4_SVR-EPO-02] ADD FILE ( NAME = N'ePO4_SVR-EPO_FG_LARGE_04', FILENAME = N'N:\MSSQL\DATA\ePO4_SVR-EPO_FG_LARGE_04.ndf' , SIZE = 41943040KB , MAXSIZE = 104857600KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [FG_LARGE]
GO
ALTER DATABASE [ePO4_SVR-EPO-02] ADD FILE ( NAME = N'ePO4_SVR-EPO_FG_LARGE_05', FILENAME = N'O:\MSSQL\DATA\ePO4_SVR-EPO_FG_LARGE_05.ndf' , SIZE = 41943040KB , MAXSIZE = 104857600KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [FG_LARGE]
GO
ALTER DATABASE [ePO4_SVR-EPO-02] ADD FILE ( NAME = N'ePO4_SVR-EPO_FG_LARGE_06', FILENAME = N'O:\MSSQL\DATA\ePO4_SVR-EPO_FG_LARGE_06.ndf' , SIZE = 41943040KB , MAXSIZE = 104857600KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [FG_LARGE]
GO

Truncate the Table EPOEvents

/****** Check Foreign Keys Referencing EPOEvents ******/USE [ePO4_SVR-EPO-02]
GO
EXEC sp_fkeys 'EPOEvents'
/****** Script Identified Foreign Keys ******/USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE [dbo].[HIP8_IPSEVENTPARAMETER] WITH CHECK ADD CONSTRAINT [FK_HIP8_IPSEVENTPARAMETER_EPOEVENTS] FOREIGN KEY([EVENTID])
REFERENCES [dbo].[EPOEVENTS] ([AUTOID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[HIP8_IPSEVENTPARAMETER] CHECK CONSTRAINT [FK_HIP8_IPSEVENTPARAMETER_EPOEVENTS]
GO
USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE [dbo].[HIP8_EVENTINFO] WITH CHECK ADD CONSTRAINT [FK_HIP8_EVENTINFO_EPOEVENTS] FOREIGN KEY([EVENTID])
REFERENCES [dbo].[EPOEVENTS] ([AUTOID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[HIP8_EVENTINFO] CHECK CONSTRAINT [FK_HIP8_EVENTINFO_EPOEVENTS]
GO
USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE [dbo].[SCOR_EVENTS] WITH CHECK ADD CONSTRAINT [SCOR_EVENTS_EPO_EVENTS] FOREIGN KEY([EPO_EVENT_AUTO_ID])
REFERENCES [dbo].[EPOEVENTS] ([AUTOID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SCOR_EVENTS] CHECK CONSTRAINT [SCOR_EVENTS_EPO_EVENTS]
GO
/****** Backup the Child Tables ******/USE [ePO4_SVR-EPO-02]
GO
SELECT * INTO HIP8_IPSEVENTPARAMETER_BAK FROM HIP8_IPSEVENTPARAMETER;
SELECT * INTO HIP8_EVENTINFO_BAK FROM HIP8_EVENTINFO;
SELECT * INTO SCOR_EVENTS_BAK FROM SCOR_EVENTS;
GO
/****** Truncate Child Tables ******/USE [ePO4_SVR-EPO-02]
GO
TRUNCATE TABLE HIP8_IPSEVENTPARAMETER;
TRUNCATE TABLE HIP8_EVENTINFO;
TRUNCATE TABLE SCOR_EVENTS;
/****** Drop Foreign Keys ******/USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE HIP8_IPSEVENTPARAMETER DROP CONSTRAINT [FK_HIP8_IPSEVENTPARAMETER_EPOEVENTS];
ALTER TABLE HIP8_EVENTINFO DROP CONSTRAINT [FK_HIP8_EVENTINFO_EPOEVENTS];
ALTER TABLE SCOR_EVENTS DROP CONSTRAINT [SCOR_EVENTS_EPO_EVENTS];
/***** Truncate EPOEvents Table ******/USE [ePO4_SVR-EPO-02]
GO
TRUNCATE TABLE EPOEVENTS;

Move EPOEvents to the New Filegroup

/***** Move EPOEvents to the New Filegroup FG_LARGE ******/
-- This is achieved by recreating the Clustered Index used by Primary Key on this table
-- First Step is to Script the Clustered Index
USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE [dbo].[EPOEvents] ADD CONSTRAINT [PK_EPOEvents_AutoID] PRIMARY KEY CLUSTERED
(
[AutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Second Step is to drop and recreate the Clustered Index after changing
-- the Filegroup the Script (observe the ON Clause in the create statement below:
-- Filegroup was previously PRIMARY and is now FG_LARGE)
-- Drop Statement
USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE [dbo].[EPOEvents] DROP CONSTRAINT [PK_EPOEvents_AutoID]
-- Create Statement
USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE [dbo].[EPOEvents] ADD CONSTRAINT [PK_EPOEvents_AutoID] PRIMARY KEY CLUSTERED
(
[AutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_LARGE]
GO

Recreate Foreign Keys on Child Tables

USE [ePO4_SVR-EPO-02]
GO
ALTER TABLE [dbo].[HIP8_IPSEVENTPARAMETER] WITH CHECK ADD CONSTRAINT [FK_HIP8_IPSEVENTPARAMETER_EPOEVENTS] FOREIGN KEY([EVENTID])
REFERENCES [dbo].[EPOEVENTS] ([AUTOID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[HIP8_IPSEVENTPARAMETER] CHECK CONSTRAINT [FK_HIP8_IPSEVENTPARAMETER_EPOEVENTS]
GO
ALTER TABLE [dbo].[HIP8_EVENTINFO] WITH CHECK ADD CONSTRAINT [FK_HIP8_EVENTINFO_EPOEVENTS] FOREIGN KEY([EVENTID])
REFERENCES [dbo].[EPOEVENTS] ([AUTOID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[HIP8_EVENTINFO] CHECK CONSTRAINT [FK_HIP8_EVENTINFO_EPOEVENTS]
GO
ALTER TABLE [dbo].[SCOR_EVENTS] WITH CHECK ADD CONSTRAINT [SCOR_EVENTS_EPO_EVENTS] FOREIGN KEY([EPO_EVENT_AUTO_ID])
REFERENCES [dbo].[EPOEVENTS] ([AUTOID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SCOR_EVENTS] CHECK CONSTRAINT [SCOR_EVENTS_EPO_EVENTS]
GO

Script the EPOEvents Table Definition

Image for post
Image for post

Backup the EPO Database

BACKUP DATABASE [ePO4_SVR-EPO-02] TO
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C01.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C02.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C03.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C04.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C05.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C06.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C07.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C08.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C09.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_C10.bak'
WITH STATS = 5, COMPRESSION, NAME = N'ePO4_SVR-EPO-02 Backup After Purge'

Shrink Datafiles in the PRIMARY Filegroup

USE [ePO4_SVR-EPO-02]
GO
DBCC SHRINKFILE (N'ePO4_SVR-EPO-01' , 40000)
DBCC SHRINKFILE (N'ePO4_SVR-EPO-02' , 40000)
DBCC SHRINKFILE (N'ePO4_SVR-EPO-03' , 40000)
GO

Backup the EPO Database

BACKUP DATABASE [ePO4_SVR-EPO-02] TO
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E01.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E02.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E03.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E04.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E05.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E06.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E07.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E08.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E09.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E10.bak'
WITH STATS = 5, COMPRESSION, NAME = N'ePO4_SVR-EPO-02 Backup After Shrink'

Drop the EPO Database

USE [master]
GO
DROP DATABASE [ePO4_SVR-EPO-02]
GO

Format Drive J

Image for post
Image for post

Restore the EPO Database with MOVE

RESTORE DATABASE [ePO4_SVR-EPO-02] FROM
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E01.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E02.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E03.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E04.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E05.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E06.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E07.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E08.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E09.bak',
DISK = 'I:\MSSQL\BACKUP\ePO4_SVR-EPO-02_22Apr2015_E10.bak'
WITH STATS = 5,
MOVE 'ePO4_SVR-EPO-02' TO 'P:\MSSQL\DATA\ePO4_SVR-EPO-02.mdf',
MOVE 'ePO4_SVR-EPO-01' TO 'P:\MSSQL\DATA\ePO4_SVR-EPO-01.ndf',
MOVE 'ePO4_SVR-EPO-03' TO 'P:\MSSQL\DATA\ePO4_SVR-EPO-03.ndf'

Relocate TempDB to Drive Q

-- Drop unnecessary TempDB files
-- The following script was run several times and required several restarts of the -- SQL instance
USE [tempdb]
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev_xx]
GO
-- Relocate TempDB filesUSE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'Q:\MSSQL\DATA\tempdev.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev_01, FILENAME = 'Q:\MSSQL\DATA\tempdev_01.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'J:\MSSQL\LOG\templog.ldf');
GO
-- Restart SQL Server
-- Confirm the location of TempDB files
SELECT name as 'File Name', physical_name as 'File Directory'
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

Start EPO Application Services

Confirm Tables Are Populated

USE [ePO4_SVR-EPO-02]
GO
SELECT COUNT(*) EPOEvents_CNT FROM EPOEvents;
SELECT COUNT(*) HIP8_EventInfo_CNT FROM HIP8_EventInfo;
SELECT COUNT(*) HIP8_IPSEventParameter_CNT FROM HIP8_IPSEventParameter;
SELECT COUNT(*) SCOR_EVENTS_CNT FROM SCOR_EVENTS;

POST CHANGE TASKS

Summary of Post Change Tasks

APPENDICES

APPENDIX I

EPOEVENTS TABLE DDL

USE [ePO4_SVR-EPO-02]
GO
/****** Object: Table [dbo].[EPOEvents] Script Date: 04/23/2015 01:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EPOEvents](
[AutoID] [int] IDENTITY(1,1) NOT NULL,
[AutoGUID] [uniqueidentifier] NOT NULL,
[ServerID] [nvarchar](16) NOT NULL,
[ReceivedUTC] [datetime] NOT NULL,
[DetectedUTC] [datetime] NOT NULL,
[AgentGUID] [uniqueidentifier] NOT NULL,
[Analyzer] [nvarchar](16) NOT NULL,
[AnalyzerName] [nvarchar](64) NOT NULL,
[AnalyzerVersion] [nvarchar](20) NOT NULL,
[AnalyzerHostName] [nvarchar](128) NULL,
[AnalyzerIPV4] [int] NULL,
[AnalyzerIPV6] [binary](16) NULL,
[AnalyzerMAC] [nvarchar](16) NULL,
[AnalyzerDATVersion] [nvarchar](20) NULL,
[AnalyzerEngineVersion] [nvarchar](20) NULL,
[AnalyzerDetectionMethod] [nvarchar](128) NULL,
[SourceHostName] [nvarchar](266) NULL,
[SourceIPV4] [int] NULL,
[SourceIPV6] [binary](16) NULL,
[SourceMAC] [nvarchar](16) NULL,
[SourceUserName] [nvarchar](128) NULL,
[SourceProcessName] [nvarchar](128) NULL,
[SourceURL] [nvarchar](1024) NULL,
[TargetHostName] [nvarchar](266) NULL,
[TargetIPV4] [int] NULL,
[TargetIPV6] [binary](16) NULL,
[TargetMAC] [nvarchar](16) NULL,
[TargetUserName] [nvarchar](128) NULL,
[TargetPort] [int] NULL,
[TargetProtocol] [nvarchar](16) NULL,
[TargetProcessName] [nvarchar](128) NULL,
[TargetFileName] [nvarchar](266) NULL,
[ThreatCategory] [nvarchar](128) NOT NULL,
[ThreatEventID] [int] NOT NULL,
[ThreatSeverity] [tinyint] NOT NULL,
[ThreatName] [nvarchar](128) NOT NULL,
[ThreatType] [nvarchar](32) NOT NULL,
[ThreatActionTaken] [nvarchar](24) NOT NULL,
[ThreatHandled] [bit] NULL,
[TheTimestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_EPOEvents_AutoID] PRIMARY KEY CLUSTERED
(
[AutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOSET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[EPOEvents] ADD CONSTRAINT [DF_EPOEvents_EventID] DEFAULT (newid()) FOR [AutoGUID]
GO
ALTER TABLE [dbo].[EPOEvents] ADD CONSTRAINT [DF_EPOEvents_ServerID] DEFAULT (N'SVR-EPO-02') FOR [ServerID]
GO
ALTER TABLE [dbo].[EPOEvents] ADD CONSTRAINT [DF_EPOEvents_ReceivedUTC] DEFAULT (getutcdate()) FOR [ReceivedUTC]
GO
ALTER TABLE [dbo].[EPOEvents] ADD CONSTRAINT [DF_EPOEvents_ThreatSeverity] DEFAULT ((1)) FOR [ThreatSeverity]
GO
ALTER TABLE [dbo].[EPOEvents] ADD CONSTRAINT [DF_EPOEvents_ThreatActionTaken] DEFAULT ('none') FOR [ThreatActionTaken]
GO

APPENDIX II

JOB CUSTOM_PURGE EPO EVENTS

USE [msdb]
GO
/****** Object: Job [Custom_Purge EPO Events] Script Date: 04/25/2015 19:47:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 04/25/2015 19:47:51 ******/
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 <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
ENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Custom_Purge EPO Events',
@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 <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step 1] Script Date: 04/25/2015 19:47:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1',
@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'DECLARE @NbLignes int ;
SELECT @NbLignes = COUNT(*) FROM EPOEvents WHERE DATEDIFF(day, DetectedUTC, GETDATE()) > ''100'';
PRINT ''Total number of lines to delete:'';
PRINT @NbLignes;
WHILE (@NbLignes > 0)
BEGIN
PRINT ''Total number of lines LEFT to delete: :'';
PRINT @NbLignes;
PRINT ''Start deleting 50000 lines'';
BEGIN TRAN DEL_SET;
DELETE TOP(500000) FROM EPOEvents WHERE DATEDIFF(day, DetectedUTC, GETDATE()) > ''100'';
COMMIT;
SET @NbLignes = @NbLignes - ''50000'';
WAITFOR DELAY ''00:05'';
END
PRINT ''End of Maintenance'' ;
',
@database_name=N'ePO4_SVR-EPO-02',
@output_file_name=N'D:\MSSQL\JOBLOG\purgeepoevents.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Purge Schedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=1,
@freq_recurrence_factor=0,
@active_start_date=20130312,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959,
@schedule_uid=N'3d0b8826-691d-4d3c-aca9-00cb5ce246b2'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Written by

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store