Server Health Indicator: Tables Extremely Close to Their Size Limit or ServerLog Indicating Arithmetic Overflow on Pathname or Filename

Products

• Carbon Black App Control (formerly Cb Protection)

Issue/Introduction

• Alert received that a table is close to approaching the  ~2.15 billion (8.9 and below) or ~4.3 billion (8.10 and above) limit.

  Health indicator Alert: Database Table Limit - There Are Database Tables on Your Server That Are Extremely Close to Their Size Limit

• In some instances, many Agents might also be showing in the Console with Approvals Out of Date in the Status column.
• In some instances, the ServerLog may also already include some references to overflow errors on inserting Pathnames or Filenames.

  Statement returned error [8115]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Arithmetic overflow error converting IDENTITY to data type int. Statement: SET NOCOUNT ON;{?=CALL dbo.InsertPathname(?)}
  Statement returned error [8115]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Arithmetic overflow error converting IDENTITY to data type int. Statement: SET NOCOUNT ON;{?=CALL dbo.InsertFilename(?)}

Environment

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

Cause

• The 'das' database will stop functioning properly if one of the tables reaches the limit.
• This condition will also affect communication between the Server/Agent, resulting in the 'Approvals Out Of Date' status. 

Resolution

1. Run SQL Server Management Studio as the Carbon Black Service Account
2. Click new Query and execute the following

   USE das;
   SELECT 
       'Pathnames' AS [Table],
       FORMAT((SELECT MAX(pathname_id) FROM dbo.pathnames WITH (NOLOCK)), 'N0') AS [Max ID],
       FORMAT((SELECT MIN(pathname_id) FROM dbo.pathnames WITH (NOLOCK)), 'N0') AS [Min ID],
       COALESCE(FORMAT((SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.UnusedPathnames') AND index_id < 2), 'N0'), 'Table Missing') AS [QTY Unused]
   UNION ALL
   SELECT 
       'Filenames',
       FORMAT((SELECT MAX(filename_id) FROM dbo.filenames WITH (NOLOCK)), 'N0'),
       FORMAT((SELECT MIN(filename_id) FROM dbo.filenames WITH (NOLOCK)), 'N0') AS [Min ID],
       COALESCE(FORMAT((SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.UnusedFilenames') AND index_id < 2), 'N0'), 'Table Missing')
   UNION ALL
   SELECT 
       'Antibodies',
       FORMAT((SELECT MAX(antibody_id) FROM dbo.antibodies WITH (NOLOCK)), 'N0'),
       FORMAT((SELECT MIN(antibody_id) FROM dbo.antibodies WITH (NOLOCK)), 'N0') AS [Min ID],
       COALESCE(FORMAT((SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.UnusedAntibodies') AND index_id < 2), 'N0'), 'Table Missing');

3. Open a case with Support, provide the results of the query, the Server Historical Logs, and request the Offline Pruning scripts.
   • Note: If on Server 8.10.4 or lower, after completing Offline Pruning an upgrade to the latest Server version will be required.