Shrinking the Das Database After File or Path Names Offline Pruning

Products

• Carbon Black App Control (formerly Cb Protection)

Issue/Introduction

How to shrink the DAS Database to free up space after running the offline pruning scripts

Environment

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

Resolution

Note: Offline Pruning creates a large amount of unused space inside the database files which will be used by SQL Server as needed, so shrinking is not always needed. Shrinking of the database files is only needed if you must free up disk space on the actual disk drive Shrinking of the database files could take significant time depending on DB size and the SQL Server performance

1. Have a recent full backup of the DAS database
2. Stop the App Control Server and App Control Reporter services
3. In SQL Server Management Studio execute the following query to locate any remaining SQL tasks connected to the das database that should be stopped:

   sp_who2

4. Expand Databases > right click das > Tasks > Shrink > Files > for each Filegroup as needed:
   
   • PRIMARY
   • SECONDARY
   • ABINST
   • ABTEMP
5. Start with Shrink Action: Release unused space
   • Release unused space only tuncates the space at the end of the file, if any, which avoids page reorganization, but majority of times it does not result in actual free disk space gain
6. Next, attempt the Shrink Action: Reorganize pages before releasing unused disk space
   • Shrink with Reorganize pages tells SQL Server to move pages inside the data file which introduces index fragmentation.
   • Shrink with Reorganize pages may be the only way to actually free up disk space
   • Shrink with Reorganize pages could take significant time to complete (make sure no other SQL tasks are running at the same time)
7. Once the shrink completes check that the initial database file sizes are set appropriately:

   Note: The Initial Size (MB) is updated automatically by SQL Server. Even if the Initial Size is set too low (e.g. 1MB) it will be automatically increased to prevent issues.

   • Right click DAS > Properties > Files > Check the "Initial Size (MB)" value for every file matches the "Space Used" value from the report:
     • Right click DAS > Reports > Standard Reports > Disk Usage > Expand "Disk Space Used by Data Files" table > check the "Space Used" value for each file
   • If the "Initial Size (MB)" is set too high > update it downwards
8. Start the App Control Server and App Control Reporter services

Additional Information

• To reduce index fragmentation after Shrink with with Reorganize pages has been done:
• • Wait for the automatic database maintnetance job to run on Saturday night
  • Manually trigger the job with this query (it is recommended to do it during off-hours):

    USE das; EXEC dbo.PerformDatabaseMaintenance

• Additional information on shrinking Microsoft SQL Server databases is available here