SCOM can cause Unchecked Growth in SSODB

Ok, that’s a bit of an attention grabbing headline, so let me clarify that statement: SCOM can cause unchecked growth in SSODB if you’re not regularly backing up the SSODB transaction log.

We encountered this one today – a client’s SSODB ran out of space overnight, causing the BizTalk environment to shut-down. On further investigation, it would appear that every time SCOM checks the health of Enterprise Single Sign-On, an entry is recorded in the SSOX_AuditXpLookup table:

ESSO appears to be clever enough to manage the size of this table, truncating it every 30 minutes, however this doesn’t help if you’re not managing the size of the database transaction log through backups. To quote the SQL Server documentation:

If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.

All the more reason to enable and run the supplied Backup BizTalk Server job to help maintain the health of your BizTalk environment.

If you’re looking for more information on the Backup BizTalk Server Job, take a look at my series of posts on the topic: http://www.modhul.com/2009/06/29/configuring-biztalk-for-disaster-recovery-part-1/

Hat-tip to this MSDN Forums posting that helped us identify the issue.

About these ads

3 thoughts on “SCOM can cause Unchecked Growth in SSODB

  1. Hi Nick,
    Did MS mentioned anywhere about this on What is exact cause of auditing happend every milli seconds. Is it by desgin or can we some how disable the auditing.
    I am so curios cos I have created a Monitoring Application using WMI like SCOM and SSODB log growth increases exponentially.
    What I have observer is
    . If I dont enable the BizTalk Backup Server job which takes the log and full backup of SSODB,
    the log file grows to a % of about 60, and automatically truncates to make the remaining space of 70 %.
    . If I enable the job this behavior is lost.
    . Also SSOX_AuditXpLookup get automatically truncated when it reaches row count of 1000.
    Please shed some light based on your experience.

    Regards
    Smithesh

  2. Smithesh,

    You are correct that the *table* is being truncated, however the records are still in the Transaction Log file, which is not truncated – only the Backup job can accomplish that.

    Unfortunately, I don’t have an answer as to *why* the auditing is happening so often, however it would appear that it occurs ever time the ESSO is interrogated for encrypted credentials.

    Nick.

  3. Thanks for your reply,
    What I obeserved is if I dont configure the BizTalk BackupServer Job, the transaction log is truncated automatically.
    It grows to 70% and then shrinks to 30%
    I verified this by checking DBCC SQLPERF(LOGSPACE).
    If I configure the job it is not automatically truncating the log transaction log file.
    Did you experience the same behavior.
    Regards
    Smithesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s