Pre-Upgrade Tasks When Upgrading to System Center 2012 R2 Operations Manager

To determine the number of rows that need to be deleted, run the following query:

  DECLARE @SubscriptionWatermark bigint = 0;     


  SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();

  Select COUNT (*)
  FROM EntityTransactionLog ETL with(nolock)
  WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL with(nolock) WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
  AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL with(nolock) WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
  AND EntityTransactionLogId < @SubscriptionWatermark;

To clean up the ETL Table, run the following SQL script:

  DECLARE @RowCount int = 1;
  DECLARE @BatchSize int = 100000;
  DECLARE @SubscriptionWatermark bigint = 0;
  DECLARE @LastErr int;

  SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();
  WHILE(@RowCount > 0)
  BEGIN 
  DELETE TOP (@BatchSize) ETL
  FROM EntityTransactionLog ETL
  WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
  AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
  AND ETL.EntityTransactionLogId < @SubscriptionWatermark;

  SELECT @LastErr = @@ERROR, @RowCount = @@ROWCOUNT;


  END

Log on to the Operations console by using an account that is a member of the Operations Manager Administrators role for the Operations Manager management group.

In the Administration pane, expand Device Management, and then click Pending Management.

Right-click each agent, and then click Approve or Reject.

Log on to the Operations console account that is a member of the Operations Manager Administrators role for the Operations Manager management group.

In the Operations console, select the Administration view.

In the navigation pane, expand Administration, expand the Notifications container, and then click Subscriptions.

Select each subscription, and then click Disable in the Actions pane.

On the Start menu, point to Administrative Tools, and then click Services.

In the Name column, right-click the Connector that you want to control, and then click Stop.

On the computer that hosts the operational database, open SQL Server Management Studio.

In the Object Explorer, expand Databases.

Right-click the operational database, point to Reports, Standard Reports, and then click Disk Usage.

View the Disk Usage report to determine the percentage of free space.

On the computer that hosts the operational database, open SQL Server Management Studio.

In the Connect to Server dialog box, in the Server Type list, select Database Engine.

In the Server Name list, select the server and instance for your operational database (for example, computer\INSTANCE1).

In the Authentication list, select Windows Authentication, and then click Connect.

In the Object Explorer pane, expand Databases, right-click the operational database, and then click Properties.

In the Database Properties dialog box, under Select a page, click Files.

In the results pane, increase the Initial Size value for the MOM_DATA database by 50 percent.

Set the Initial Size value for the MOM_LOG to be 50 percent of the total size of the database. For example, if the operational database size is 100 GB, the log file size should be 50 GB. Then click OK.