Running Offline Pruning on the Filenames or Pathnames Tables
search cancel

Running Offline Pruning on the Filenames or Pathnames Tables

book

Article ID: 370589

calendar_today

Updated On:

Products

Carbon Black App Control (formerly Cb Protection)

Issue/Introduction

Steps to execute the Offline Pruning Scripts for the filenames or pathnames tables.

Environment

  • App Control Server: All Supported Versions
  • Microsoft SQL Server: All Supported Versions

Cause

  • Offline pruning of the filenames or pathnames tables should be done when those tables are near or have reached their limit of 2.14 Billion rows.
  • Offline pruning is also recommended when the filenames or pathnames tables have grown too big and have a large percentage of orphaned data thus causing performance and disk space issues
  • These steps should only be completed as advised by Support.

Resolution

  1. Before proceeding with the steps
    • Open a case with Support and request the relevant Offline Pruning Script for the table/Server being pruned.
    • Plan for a 24-hour maintenance window while the Pruning Script executes.
    • Take a known-good, full backup of the DAS database.
  2. Prepare for Offline Pruning Execution
    1. Log in to the application server hosting the Console as the Carbon Black Service Account.
    2. If an Agent is installed on the application server, temporarily stop and unload the Agent to prevent Tamper Protection blocks.
    3. Stop (or temporarily disable) the services for the App Control Reporter and App Control Server.
    4. Any external application that accesses the das database should be temporarily stopped.
      • This includes things like the SQL Job Agent, backups, reporting, etcetera.
      • The SQL Server service itself should remain running, however.
  3. Initiate the Offline Pruning
    1. Extract the OfflineFilePathnamePruning.zip locally (ex: C:\Temp\)
    2. Double click the relevant bat file (ex: RunOfflinePruning.bat) (do not use "Run as Administrator")
    3. Input the SQL Server\SQL Instance name
      • Single Tier (local database): Enter a single dot   .
      • Two Tier (remote database): Enter the SQLServer\SQLInstance name, example: SomeSQL\SomeInstance
    4. Wait for Connection Tests to complete
    5. Once successful, and prompted, enter Y to proceed.
    6. IMPORTANT: Do not interrupt the process or access the das database during this time.
      • Doing so will require a full database restore and restarting the process.
  4. Upon Completion the command prompt will include the STOP Time and instructions
    • Be sure to copy the output of the command line window to a text file before closing it.
    • Do not yet start any services.
  5. Reclaim Filename/Pathname IDs for re-use
    1. Run SQL Server Management Studio as the Carbon Black Service Account
    2. First, because the Agents have a backlog of filenames, increase the Chunk Size for the related Scheduled Tasks 
      USE das;
      EXEC dbo.UpdateShepherdConfig 'ReuseIdsMinTableSize', 200000000;
      EXEC dbo.UpdateShepherdConfig 'UnusedFilenameIdFinderChunkSize', 100000000;
      EXEC dbo.UpdateShepherdConfig 'UnusedPathnameIdFinderChunkSize', 100000000;
    3. Use a New Query window and execute the following commands against the DAS database while the AppC Server and Reporter are still stopped.
      • For App Control Server 8.10.4 and higher: 
        EXEC dbo.UnusedFilenameIdFinder 0;
      • For App Control Server 8.10.0 and prior
        1. Determine the task_id for UnusedFilenameIdFinder and UnusedPathnameIdFinder 
          SELECT task_id, task FROM scheduled_tasks (NOLOCK) WHERE task IN ('UnusedFilenameIdFinder','UnusedPathnameIdFinder');
        2. Adjust the query to execute each Stored Procedure with the relevant task_id for each task (ex with 47 and 48) 
          EXEC dbo.UnusedFilenameIdFinder 47; 
          EXEC dbo.UnusedPathnameIdFinder 48;
    4. Wait for the tasks to complete.
    5. If desired, shrink additional tables to reclaim diskspace at this time.
  6. Start services for the App Control Server and App Control Reporter.
    1. Verify the Console is accessible and Agents are beginning to reconnect.
    2. Restart the App Control Agent (if stopped) and any other SQL Services, tasks, etc.

Additional Information

Powered by Wolken Software