McAfee ePO4 Database maintainence

ePO Database size usually grows very fast in my case it had grown to 40 Gb in just couple of months. To keep the size below 20Gb i had to perform some steps for which i had created a sql server 2005 T-SQL script, you can copy and optimize/change it as per your need. Please find the script below.

----------------------------------------------------

-- BACKUP Database on Disk\Network Location

----------------------------------------------------

USE Master;

-- Database backup with INIT option will replace the existing file in the backup folder

-- to avoid this comment 'WITH INIT' option from the next line

DECLARE @@LOCATION nVARCHAR(100)

set @@LOCATION = ('disk_or_network_location\epo4database-'+convert(varchar, getdate(), 112)+'.bak')

BACKUP DATABASE [ePO4_DATABASE] TO DISK = @@LOCATION WITH INIT

GO

----------------------------------------------------

-- Clear Database for keeping only one month records

----------------------------------------------------

USE [ePO4_DATABASE];

GO

delete from EPOEvents where ReceivedUTC < (getdate() - 31)

GO

----------------------------------------------------

-- Truncate EPO Database to 4 MB

----------------------------------------------------

USE [ePO4_DATABASE];

GO

-- Set database recovery model to SIMPLE.

ALTER DATABASE [ePO4_DATABASE]

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 4 MB.

DBCC SHRINKFILE ([ePO4_DATABASE, 4);

GO

-- Set database recovery model to FULL.

ALTER DATABASE [ePO4_DATABASE]

SET RECOVERY FULL;

GO