Service Manager Data Warehouse databases getting very big

  • Article

SCSM Data Warehouse jobs may fail because the DW databases run out of disk space. You can verify this by looking in Operations Manager event log entries on the DW Management server for errors. You can also directly go to the SQL Server machine and check for free disk space.

If there is no more space, then these are your options:

This article is about the Recovery Model setting. For more detail about SQL Server Recovery Models: https://msdn.microsoft.com/en-us/library/ms189275(v=sql.110).aspx 

At the time you installed SCSM DW, most of the DW databases are created with Recovery Model as Full. As you can read the details in the article above, this is good if you need to recover to an arbitrary point in time. This option has a consequence that the disks will fill up quickly because the log files of the DW databases will grow forever unless you do log backups.

In point of view of SCSM, taking daily backups should be sufficient. Therefore, changing the Recovery Model for the DW databases to Simple is OK.

I am explaining below the steps only for the DWStagingAndConfig databases. Please repeat the exact steps for the other DW databases, too. (CMDWDataMart, DWDataMart, DWRepository, OMDWDataMart).

In SQL Server Management Studio connect to the SQL instance which is hosting the DW databases and expand “Databases”.

  1. Right click on DWStagingAndConfig and select Properties.
    image
  2. Select Options (on the left menu).
  3. Check Recovery model on the right (should be the 2nd drop down list).
    image
  4. If the value is Simple then you are OK with this database. Just click Cancel and go to step #1 and repeat the steps for the other DW databases.
  5. If the value is Full then change it to Simple and select OK.
  6. Right click the database again and select Tasks > Shrink > Files
    image
  7. Select Log in File type:
    image
  8. Click OK.

After you repeat all the steps above for all the 5 DW databases please check now the available disk space.

Hopefully the DW jobs will now run successfully.

Thanks

Kubilay Hüsmenoğlu