Database size issue

Usage and Config Platform
1

Recently noticed it seems my BigFix database has gotten out of control.

Its data file is currently 156GB, for about 230 clients.I seem to remember this database only being about 10GB or less earlier this year, not sure what is causing the huge growth.

Looking at the database tables, it seems I have 2 tables in particular taking up all the space.
“EXTERNAL_OBJECT_DEFS”, and “BLOBFIELDS”. Together they represent 98% of the space being used.

In looking at the EXTERNAL_OBJECT_DEFS table, I found that the vast majority of the records have a “Sitename” that starts with “Enterprise Security__xxxx” with “xxxx” being some number. There are roughly 13000 - 14000 records per sitename, with about 230 unique sitenames that start with this. The BLOBFIELDS table as well shows these “Enterprise Security” sitenames.

I have ran all the admin tool cleanups, as well as having tried to shrink the database. This got me a couple GB’s back only.

Edit
Ok it seems the number at the end of “Enterprise Security” refers to the version number. And this sitename is actually “Patches for Windows”. So its the current version of this site. Which apparently from what I’m seeing I’m a few hundred versions behind for some reason…

Edit
Ok I figured this one out today. To make a long story short I had the “Patches for Windows” site enabled. At some point in the past several months/years ago the import process for this site started failing possibly due to database corruption. However it was getting far enough along to at least partially import the new data and take up space in the database. But since the process was not able to finish it was not doing any sort of cleanup on prior imports.

I was able to disable the site and BigFix then automatically deleted all related records.

Fixed the database consistency issues, shrunk it back down to size, re-indexed, etc… and re-enabled the site. After which it imported without any further issue.

2

Growth like that is usually caused by some number of Analyses returning huge results. In particular some of the CIS checklist “Measured Values” can return huge results, like the checks that list all the files that are owned by a UID or GID that are not resolvable.

If that’s the case you could deactivate those analyses, but I don’t think there’s an in-box method to remove the old results yet.

3

going thru this actually …
try to identify the biggest analyses thru the relevance:

select
e.Sitename, e.Name, l.computerid, l.AnalysisId, DATALENGTH(l.ResultsText) AS
[Size]
from LOCAL_OBJECT_DEFS e
left join LONGQUESTIONRESULTS l on l.AnalysisID = e.ID
where ComputerID is not NULL
and e.IsFixlet = 1 and dbo.fn_ExtractField( 'Fixlet Type', 0, e.Fields ) =
'Analysis'
order by 5 desc

Once identified, stop the services, do a BFEnterprise backup and remove the large analyses results… something like:

DELETE FROM [BFEnterprise].[dbo].[LONGQUESTIONRESULTS] where siteID = 123 and analysisID = 123