I've been having problems with the OpsManager logfile lately. a while back i was getting the errors
Could not write a checkpoint record in database ID 8 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
The transaction log for database 'OperationsManager' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc
column in sys.databases
And after looking i couldent find out why it was doing this; plus it had gotten to a point where i couldent shrink the logfile so
yesturday i detached the db, renamed the log and reattached the DB and let it create a new log. after cleaning that up it seemed to work fine... until last night, when the logfile exploded to 17 gb (when the main opsmgr db was 5gb. This morning after cleaning
up the drive a bit, i ran the filesize shrink and now the main db is 1.7gb, and the logfile is 5gb which still seems high but much more reasonable. It seems like OpsMgr isnt cleaning up the SQL logfile properly, nor tending to its own DB.
If you treat the OpsDB for SCOM like "any other database" then your results will not meet expectationsa. Unfortunately - you are making some critical mistakes with this environment:
*NEVER* shrink an OperationsManager database file. This fragments the file and is difficult to recover from.
You should scope the size of the OpsDB based on agent count, MP's in use, and plan for appropriate free space. I recommend even the smallest implementations use a 30GB OpsDB file by default. The reason for this is that the operations DB needs
40-50% free space at all times for maintenance operations. These operations are included in the product, and the SQL DBA team should not perform any additional ones without taking this into account. Next - I like the 30GB file size pre-allocated,
because this stops any chance of file fragmentation on disk caused by auto-growths or manual growths over time. This 30GB file also allows for some level of "uh-oh" factor in case the grooming fails for some time before you catch it.
Next - the TLOG sizing. My rule of thumb is 50% of the DB file size for small DB's (30GB or less) and 20% of the DB file size for large DB's (100GB). So for yours - set it to 15GB. This gives it PLENTY of free space for maintenance operations
and insert activity.
Now - that said - as to why yours had to grow to 17GB - that is very odd. In all the DB's I have supported - the only time we ever say this happen, was caused by the DBA's built in maintenance. It was either a reindex, backup job, or statistics
job taking forever due to an issue, like blocking cause by your maintenance and our maintenance running at the same time, SQL disk I/O issues, or hung SQL agent jobs. This blocked an operation from committing and kept the log from checkpoint. The
condition is very rare and I almost guarantee there is something very unique about your environment to cause this.
Take a look at:
http://blogs.technet.com/b/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx
and
http://blogs.technet.com/b/kevinholman/archive/2010/06/16/management-group-checkup-a-database-perspective.aspx
It sounds like you have a pretty small environment from the 1.7GB DB file size.... so its going to be a configuration issue.
