Repairing McAfee's EPO MSDE/SQL Express Database

The first issue I encountered with this is that McAfee wouldn't own up to why the size of the database couldn't be controlled with the tools and procedures they provide.  Since there is a 2GB size limit on the MSDE (a.k.a. SQL Express) database, it doesn't take much effort for a runaway table to bring the database down.  I knew enough from previous efforts that the 'EventDayResolution' table was the issue, but not being enough of a SQL DBA, I didn't know why this was and my efforts to extract information from McAfee's e-mail support proved at best, a waste of time.

I finally resolved to tackle the issue with the assistance of the GUI management tool that comes with SQL Server 2005.  Although not neccesarily required to run the repairs, I eventually needed it to perform one task for which I couldn't track down a script command to use.  If you do not have access to the tool, you can download a demo of SQL and use that.

The problem I encountered while trying to use the McAfee procedures was twofold:

1. The 'EventDayResolution' table had grown excessivly large (several hundred megabytes).
2. The indexes for the table had grown to roughly double the size of the table they represented.  This was caused by this helpful McAfee suggestion that didn't take into account the fact that their canned tool that is used to purge events fails to purge data from the 'EventDayResolution' table and the ensuing reindex leads to a double wammy against the space restrictions of the MSDE database.

As near as I can tell, the suggested McAfee path for fixing these issues would be:

1. Using the purge events tool from within the EPO program

2. Manually purging the data from the 'EventDayResolution' table
3. Shrink the database
4. Reindex

Although the first three might work, the forth one would consistently bomb for me since SQL needs double the space of the records that are to be purged.  If I recall correctly, a copy is made before the record is purged.  On a different database package that I had used in the past it supported a "no fault tolerant" switch where such commands would run without doing this; but I was unable to find an equivalent within Microsoft SQL.  To make up for this shortcoming I used the SQL GUI management tool to delete the indexes and recreate them  (I wasn't able to find an OSQL statement that could be used to DELETE the indexes.  DROP yes, but not DELETE) .  Where possible, backup your database first before doing this by using either the GUI tool that comes with the EPO or by using OSQL commands.  (I'll say now that this tip is provided with no warranty what-so-ever since an incorrect step can royally trash your database.  I know this because I did just that myself!).

Before deleting the index, you'll want to create a query that will recreate the indexes.  I did this by right clicking on the indexes, going to 'script index as' and then 'create to'.  I then compiled all the commands into one file and produced this query:

USE [ePO_SERVER]

GO

/****** Object:  Index [IX_EventDayResolution_NodeID]    Script Date: 06/05/2006 13:25:51 ******/

CREATE CLUSTERED INDEX [IX_EventDayResolution_NodeID] ON [dbo].[EventDayResolution]

(

 [NodeID] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_VirusActionProduct]    Script Date: 06/05/2006 13:15:57 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_VirusActionProduct] ON [dbo].[EventDayResolution]

(

 [ActionTaken] ASC,

 [VirusName] ASC,

 [ProductName] ASC,

 [ProductVersion] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_User]    Script Date: 06/05/2006 13:26:36 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_User] ON [dbo].[EventDayResolution]

(

 [UserName] ASC,

 [VirusName] ASC,

 [ProductName] ASC,

 [ProductVersion] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_Host]    Script Date: 06/05/2006 13:27:08 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_Host] ON [dbo].[EventDayResolution]

(

 [HostName] ASC,

 [VirusName] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_Day]    Script Date: 06/05/2006 13:27:31 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_Day] ON [dbo].[EventDayResolution]

(

 [YearMonthDay] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_Dates]    Script Date: 06/05/2006 13:27:49 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_Dates] ON [dbo].[EventDayResolution]

(

 [YearMonth] ASC,

 [YearWeek] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_DateDetectAction]    Script Date: 06/05/2006 13:28:06 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_DateDetectAction] ON [dbo].[EventDayResolution]

(

 [ActionTaken] ASC,

 [VirusName] ASC,

 [YearWeek] ASC,

 [YearMonth] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_DateDetect]    Script Date: 06/05/2006 13:28:21 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_DateDetect] ON [dbo].[EventDayResolution]

(

 [YearWeek] ASC,

 [YearMonth] ASC,

 [VirusName] ASC,

 [ProductName] ASC,

 [ProductVersion] ASC

) ON [PRIMARY]

/****** Object:  Index [IX_EventDayResolution_AutoID]    Script Date: 06/05/2006 13:28:45 ******/

CREATE NONCLUSTERED INDEX [IX_EventDayResolution_AutoID] ON [dbo].[EventDayResolution]

(

 [AutoID] ASC

) ON [PRIMARY]

Having my query in hand, I right click on the indexes and delete them (saving the 'clustered' index for last) and then perform the database shrink detailed here.  Afterwards, I run the query to recreate the indexes and I then run a reindexing on the database.

(On a side note, the whole 'EventDayResolution' table can be purged and recreated using the same procedure detailed above for the indexes.  I needed to do this myself since the purging of the 'EventDayResoultion' table caused a copy of it to be created within the SQL system 'temp' database on a volume on which there wasn't enough room to do so.  This is how I trashed the database on my first attempt since the purge bombed halfway through and the MSDE database was then too large (4GB) to replay the log after the crash).

Additional Notes:

• The link for manually purging table information doesn't specify, but I believe a leading zero is needed for single digit months, etc.

• This article details how to backup the EPO database using different methods.  I use a batch file with the 'osql' command and run a nightly backup so that it makes it onto tape.
• This article covers restores (most easily accomplished by using McAfee's DBBak.exe utility).

This page was last modified on June 16, 2006 10:59 AM