McAfee EPO : Database Managment
I recently ran into some issues with a remote sites EPO server. There were seveal issues:
- SQL Express Database had grown to 2GB (it was SQL Express 2005 therefore 2GB was the limit)
- The drive which the database files were stored was running low on disk space
- 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)