DCS Events/Data on the database volume are growing causing low disk space

Products

• Data Center Security Server Advanced
• Data Center Security Server
• Data Center Security Monitoring Edition

Issue/Introduction

DCS data retention in the DB server. We notice the DB file is growing exponentially with no sign of deleting aged data (e.g. events are configured to purged), and this in return causes an increase in the disk space utilized by the DB.

 

Environment

Data Center Security

Release : 6.9.1, 6.9.2, 6.9.3

Cause

SQL DB partitions need to be shrunk as by default they are only used for 1 day's worth of data and recycled every 2 years. Shrinking the partition does not remove any events, it releases drive space.

Resolution

A new feature has been added to DCS 6.9.2 and higher that allows you to shrink only empty partitions in the database.

DCS 6.9.2 and higher Database Partition Shrink procedure with resize options

Where is the stored procedure?
The stored procedure is compiled into SQL Server and stored in the DB during the upgrade (or fresh install)

What commands do I use to run this procedure?
Test Mode
/* Running procedure in TESTMODE will show how much space is released*/
EXEC SCSP_ShrinkDbPartitions @eventClass='REALTIME', @dataFileShrinkTargetSizeInMB=50, @shrinkMode='TESTMODE'; 
EXEC SCSP_ShrinkDbPartitions @eventClass='PROFILE',  @dataFileShrinkTargetSizeInMB=1, @shrinkMode='TESTMODE';
EXEC SCSP_ShrinkDbPartitions @eventClass='ANALYSIS',  @dataFileShrinkTargetSizeInMB=1, @shrinkMode='TESTMODE';

Shrink Mode
/* Running procedure to actually SHRINK only those empty partitions not in use*/
EXEC SCSP_ShrinkDbPartitions @eventClass='REALTIME', @dataFileShrinkTargetSizeInMB=50, @shrinkMode='SHRINK';
EXEC SCSP_ShrinkDbPartitions @eventClass='PROFILE',  @dataFileShrinkTargetSizeInMB=1, @shrinkMode='SHRINK';
EXEC SCSP_ShrinkDbPartitions @eventClass='ANALYSIS',  @dataFileShrinkTargetSizeInMB=1, @shrinkMode='SHRINK';

How often should we run the stored procedure
We recommend you run this at least monthly and no more then weekly.