Sorry this is long winded... I'm new to my current environment and to SCOM tuning (SQL Server 2005. The OpsManager DB was around 15GB with a tran log which would sometimes grow to around 9GB due to conflict with an existing maint plan which was rebuilding the indexes. I removed Opsmanager/OpsManagerDW from the existing maint plan to stop the log growth and allow the builtin SCOM maintenance to handle grooming etc. I changed the recovery mode for OpsManager/OpsManagerDW from full to simple mode per Kevin Holman's articles. Also per his articles, I gave OpsManager its own index rebuild maint plan. Now Opsmanager is around 26GB with free space around 11GB. It now almost consumes all the disk space allocated on the volume. (Unforetunately, I didn't have a good baseline so I don't know what free space the DBs had prior to changing full to simple.) So in general, does a DB grow that much and require that much free space when recovery mode is changed from full to simple? Can I shrink the database to reduce the amount of free space which seems alot? Should I get rid of the new weekly index rebuild job? Thanks.

It's recommended to have an OpsDb around 30Gb with at least 40% of freespace, so for me, your configuration is good :)Christopher Keyaert - My OpsMgr/SCOM blog : http://www.vnext.be

Hi 1) So in general, does a DB grow that much and require that much free space when recovery mode is changed from full to simple? No - this should not happen. Recovery mode only affects the transaction log and not the data file. Basically with simple recovery mode, SQL truncates (maintains) the transaction log so that you can't use it to recovert the database to point in time. With full, you need to backup the transaction log to truncate it. So in fact, full recovery mode should require more space. It is not changing the recovery mode that has influenced disk space. 2) Can I shrink the database to reduce the amount of free space which seems alot? This is not recomended due to the likelihood of database fragmentation that will really hit performance. 3) Should I get rid of the new weekly index rebuild job? From - http://blogs.technet.com/b/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx There is no need for a DBA to execute any UPDATE STATISTICS or DBCC DBREINDEX maintenance against this database. Furthermore, since we run our maintenance every 60 seconds, and only execute maintenance when necessary, there is no "set window" where we will run our maintenance jobs. This means that if a DBA team also sets up a UPDATE STATISTICS or DBCC DBREINDEX job - it can conflict with our jobs and execute concurrently. This should not be performed. Cheers GrahamView OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

Hi 1) So in general, does a DB grow that much and require that much free space when recovery mode is changed from full to simple? No - this should not happen. Recovery mode only affects the transaction log and not the data file. Basically with simple recovery mode, SQL truncates (maintains) the transaction log so that you can't use it to recovert the database to point in time. With full, you need to backup the transaction log to truncate it. So in fact, full recovery mode should require more space. It is not changing the recovery mode that has influenced disk space. 2) Can I shrink the database to reduce the amount of free space which seems alot? This is not recomended due to the likelihood of database fragmentation that will really hit performance. 3) Should I get rid of the new weekly index rebuild job? From - http://blogs.technet.com/b/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx There is no need for a DBA to execute any UPDATE STATISTICS or DBCC DBREINDEX maintenance against this database. Furthermore, since we run our maintenance every 60 seconds, and only execute maintenance when necessary, there is no "set window" where we will run our maintenance jobs. This means that if a DBA team also sets up a UPDATE STATISTICS or DBCC DBREINDEX job - it can conflict with our jobs and execute concurrently. This should not be performed. Cheers GrahamView OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/