SQL Server database optimization

(imported topic written by rdamours91)

Just wondering how everyone has their BFEnterprise databases set in SQL Server Enterprise Manager. Is anyone using the database maintenance plan wizard to reorganize data and index pages, update statistics and remove unwanted space from databases. I see an option in there to reorganize data and index pages to 10% free space or leave at original free space, etc.

Just wondering if there is a preferred way to maintain the databases as she’s starting to become a bit of a pig lately. My BFEnterprise database is about 48GB (17,000 clients).

Right after I do a backup it seems to wakeup as it dumps the transaction logs…but after a couple of days it’s sluggish again.

(imported comment written by BenKus)

48GB! That is very high!

As a reference point, I was just working with one of our 190,000 computer deployments and their database size is 14 GB.

Probably the bulk of that 48GB is the transaction log (which is generally considered unnecessary for the BES Server and will grow quickly). You should be using “Simple Mode” for the transaction log.

Also, you can usually shrink the database to get the size down.

Some more suggestions are here: http://support.bigfix.com/bes/misc/besperformance.html

Couple more notes:

  • The BES Server installer puts in a periodic job to run re-indexing on the most important tables periodically. It is not a bad idea to add a daily or weekly maintenance job to reindex all tables.
  • Don’t forget to do backups.
  • Generally shrinking a database is unnecessary, but if they get too big (like yours is currently), you can shrink it to reclaim lots of disk space.

There are lots of performance tuning notes in this forum. Have you looked through those?


(imported comment written by rdamours91)

I’ve got it set to simple mode and the transaction logs are only about 400MB.

I’ll look into the database shrink…hopefully that will reclaim some space and wake it up a bit. I’ve got weekly backups going but the nightly might not be a bad idea either.

I’ll keep digging to see what other tips there are lurking out there…

(imported comment written by BenKus)

So you have a 48 GB database for only 17,000 computers and only 400MB transaction logs? That is very high…

Can you tell me the following:

  • How many open actions do you have?
  • Do you make lots and lots of custom properties?
  • How many Fixlet sites are you subscribed to?
  • How many activated Analyses do you have?

Perhaps you should contact support to see if someone can take a look and figure out what is taking up so much space.


(imported comment written by rdamours91)

About 400 open actions, 600 expired actions…though I’m chopping those down.

Not very many custom properties at all, subscribed to 12 sites, only about 8 activated analyses as I did not to burden the systems heavily.

I think part of it is Deepfrozen (Faronics) workststions showing up as duplicate computers…that get deleted on a regular basis. These stations are probably still part of the database until they are deleted…as they probably show as flagged but not actually deleted.

(imported comment written by jessewk)

You might try running the deleted computer remover:


It is recommended that you use the ‘-r’ flag.

(imported comment written by rdamours91)

Running it now actually …

Looking for machiones that haven’t reported in 2 months with the refresh and logfile.

(imported comment written by rdamours91)

I had to go back to pick machines that haven’t reported in a year, then decrement month by month with backups and shrinks of the database and transaction logs in between. Untill we can get sites to get rid of Deepfreeze this will have to be an ongoing process.

I’ve knocked a number of GB’s out of the database already…looks like I’m going in the right direction.

(imported comment written by rdamours91)

Knocked 10 GB off the database size so far. Richard Simmons would be proud of the weight los…

(imported comment written by BenKus)

Cool… But I would have expected more because your database is still very big…

Note that I have heard that Faronics may have some way to make this issue not occur by having DeepFreeze save the BES Client state and then restore it at startup. I would contact Faronics for more info.


(imported comment written by rdamours91)

Another topic…

I just posted I’m looking for the Directory structure of the Bigfix Enterprise\Bes Server folder.

One of the guys droppped one the these Bes Server folders into one of the subfolders and I am now getting the error.

08 Dec 2006 20:31:58 -0700 - ( PostResults: (/cgi-bin/bfenterprise/PostResults.exe) Error: class DirectoryNotFoundError

Fri, 08 Dec 2006 20:31:58 -0700 - ( PostResults: (/cgi-bin/bfenterprise/PostResults.exe) Error: class DirectoryNotFoundError

Fri, 08 Dec 2006 20:31:58 -0700 - ( PostResults: (/cgi-bin/bfenterprise/PostResults.exe) Error: class DirectoryNotFoundError

Fri, 08 Dec 2006 20:31:58 -0700 - ( PostResults: (/cgi-bin/bfenterprise/PostResults.exe) Error: class DirectoryNotFoundError

Fri, 08 Dec 2006 20:31:58 -0700 - ( PostResults: (/cgi-bin/bfenterprise/PostResults.exe) Error: class DirectoryNotFoundError

Fri, 08 Dec 2006 20:31:58 -0700 - ( PostResults: (/cgi-bin/bfenterprise/PostResults.exe) Error: class DirectoryNot

I’ve since knocked down the size of the database another 10 GB

(imported comment written by rdamours91)

No more errors with the directory not found.

Knocked another 5GB down off the DB size with with shrinks and backups. Kinda like Anna Nicole with the whole Trimspa thing.