McAfee EPO : Database Managment

McAfee EPO : Database Managment

I recently ran into some issues with a remote sites EPO server. There were seveal issues:

  1. SQL Express Database had grown to 2GB (it was SQL Express 2005 therefore 2GB was the limit)
  2. The drive which the database files were stored was running low on disk space
  3. There were excessive rows in the events table which required purging

Move The Database

— Identify logical file names and file locations for the database you wish to move

select a.name, b.name as ‘Logical filename’, b.filename from sys.sysdatabases a

inner join sys.sysaltfiles b

on a.dbid = b.dbid

 

— Set DB Offline, forcefully

ALTER DATABASE ePO4_SERVER

SET offline WITH  ROLLBACK IMMEDIATE

GO

 

— Copy files to the new location, yes COPY!

— New location D:\Program Files\McAfee\ePO SQL Database

— Old Location C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

— Set Destination NTFS Permissions to Match Source location

 

— Move database files(As there was not enough disk space on the C:\ on SERVER)

ALTER DATABASE ePO4_SERVER

MODIFY FILE ( NAME = ePO4_SERVER,

FILENAME = “D:\Program Files\McAfee\ePO SQL Database\ePO4_SERVER.mdf”)

GO

 

ALTER DATABASE ePO4_SERVER

MODIFY FILE ( NAME = ePO4_SERVER_log,

FILENAME = “D:\Program Files\McAfee\ePO SQL Database\ePO4_SERVER_log.LDF”)

GO

 

ALTER DATABASE ePO4_SERVER

SET online

GO

Cleanup The Database


— Find Out EPO DB Size

EXEC sp_spaceused

 

— Find Table Sizes

select object_name(id) [Table Name],

[Table Size] = convert (varchar, dpages * 8 / 1024) + ‘MB’

from sysindexes where indid in (0,1)

order by dpages desc

 

— Identify Events that can be purged between a specific date/time range

Select ThreatEventid, Count(Threateventid)

FROM EPOEvents

WHERE ReceivedUTC BETWEEN ‘2000-01-01 00:00:00.000’ AND ‘2011-03-31 23:59:59.999’

GROUP BY ThreatEventid

 

— Purge Events

 

— 1 Stop all EPO Sevices

— 2 Backup DB

— 3 Purge table

 

DELETE FROM EPOEvents

WHERE ReceivedUTC BETWEEN ‘2000-01-01 00:00:00.000’ AND ‘2011-05-31 23:59:59.999’

 

— 4 shrink the database

dbcc shrinkdatabase (ePO4_SERVER,1)