02 February 2012

Sophos Enterprise Console error retrieving information from the database

The Symptom
When attempting to acknowledge alerts in the Enterprise console you might run into the following error:

An Unexpected error has occurred
An unexpected error while retrieving the information from the database

Clicking on the advanced button show the following:

Could not allocate space for object 'dbo.Threats'.'IX_Threats_Status_ThreatInstanceID' in database 'SOPHOS45' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
----- [outer exception] -----
   -- error: 0x00000E14 (Unknown error)
   -- facility: Generic (System)

The cause
The data base actions cannot be completed the because the database is out of free space.  This can because of one of two things.

Option 1  - You have physically run out of space for the database
Option 2  - The Sophos database has reached the maximum allowed file size for SQL Express.

The default for Sophos is to install with a local SQL express database. Generally the database would remain fairly small so a 4GB limit should suffice for most usage cases.  It is also a pain to setup with a separate stand alone SQL server...

If you connect to the SQL instance you view the database general properties you would see something similar to this:

Looking at the files you will see that the database file group file has grown to the maximum size

Attempting to increase the size will give you the following error

The fix
The database should not get this big unless something has gone wrong or has been configured incorrectly.

Checking the reporting configuration I noticed that we were not purging events.
From the Enterprise Console -->Tools --> Configure Reporting.

I set the option to purge alerts older than 3 months.

I gave it a bit of time to set in - then restarted the SQL service.  A few minutes later I was once again able to acknowledged alerts.

Checking back in I also saw that the free space available had increased significantly.

If however you would want to keep older events or the database legitimately grows to over 4GB you will have to upgrade from using SQL Express to using a full version of SQL

More Info
I also found the following and it might be useful so I will post it here.  If you want to manually kick off the Purge Task and the shrink that database here are the steps.

Go to command prompt of Management server.
#To purge database
OSQL -E -S  .\SOPHOS -d sophos45 -Q "exec PurgeTask"

#To shrink Database
2> GO
from http://community.sophos.com/t5/Sophos-Endpoint-Protection/Sophos-Enterprise-Console-error/m-p/1829#M996

No comments:

Post a Comment