Occasionally DCS Alerts are not being received from clients until the server or services are restarted

Products

• Data Center Security Server Advanced

Issue/Introduction

Occasionally DCS Alerts are not being received from clients until the server or services are restarted

Cause

There were possible causes:

1. The "Symantec Data Center Security Server Manager" service is started but never successfully connects or stays connected to the database after a reboot or other events.

* The database engine is on the DCS server which often creates a condition where SQL will not be started until way after the "Symantec Data Center Security Server Manager" service has started. This leads to the service failing to connect to the database because, currently, it doesn't have much ability to keep retrying (needs to be addressed in a future release).

* When loading bulk events in UMC it was taking a long time to load. Progress indicator seemed to spin and spin indicating long query times on the back end.

SUGGESTION:  Change the service's start type from Automatic to Automatic (Delayed).

2. The size and or condition of the database are questionable causing the DCS Manager to be unable to commit the events to the database. This seemed to be (in this case) the most  likely cause.

Running the following SQL query against the database showed high fragmentation of the event tables. These tables do get fragmented quickly but the database maintenance plan was never configured to reorganize the indexes at run-time.

select
 [Table] = object_name(stats.[object_id])
,[Index]= si.[name]
,[Avg Fragmentation] = cast(stats.[avg_fragmentation_in_percent] as numeric (6,2))
,[Page Count] = stats.[page_count]
,[Index Type] = [index_type_desc]
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats
join sys.indexes as si on stats.object_id = si.object_id
 and stats.[index_id] = si.[index_id]
where stats.[avg_fragmentation_in_percent] > 10.0
  and si.[index_id] > 0
  -- and stats.[page_count] >= 50
order by [Avg Fragmentation] desc,[Page Count] desc

The above query showed results similar to the following:

Top 7 rows (of 64 highly fragmented indexes)

Table                                            Column/Index                                         Fragmentation Percentage  Pages

CSPEVENT                                 IDX_CSPEVENT_AGENTNAME_ASC     99.97                                      3138
CSPEVENT                                 IDX_CSPEVENT_AGENTNAME_DESC   99.97                                      3129
CSPEVENT                                 IDX_CSPEVENT_ASSET_RID                 99.94                                      1691
CSPEVENT                                 IDX_CSPEVENT_ASSET_RID                 99.94                                      1682
CSPEVENT                                 IDX_CSPEVENT_ASSET_RID                 99.94                                      1650
CSPEVENT                                 IDX_CSPEVENT_ASSET_RID                 99.94                                      1648
CSPEVENT                                 IDX_CSPEVENT_AGENTNAME_DESC    99.91                                     3204

Environment

Release : 6.8 MP2

 

Resolution

1. Manually defragment the database (attached to this article) Note: As with all scripts run externally against the database a current backup would be prudent, but the script is safe.  It would also be best to stop the services on the DCS server first.
2. Manually shrink the database because the current maintenance plan or routine isn't getting the job done; so it must be done manually a little at-a-time via SQL Management Studio.
3. Once the database is defragmented and shrunk down-- make sure the maintenance plan or routine being employed is set to do it correctly going forward.
4. Purge out any old events that really aren't needed via the UMC console under the "Settings" tab. Review retention settings and adjust to the lowest allowable setting. The longer data is retained the larger the database will grow and sometimes become problematic.