Environment
- App Control: All Versions
Symptoms
- Backlog not decreasing, but is changing
- Analysis script shows Total Antibody backlog and Total count of temp AB's on deleted or disabled hosts to be very close
- Processing backlog does not seem to be impacted, new data is importing correctly
Cause
There is logic which causes the Temp Antibodies for deleted devices to not import, and instead skip to devices which are not deleted. This causes them to sit in the backlog without importing or deleting.
Resolution
Warning: Running the steps below, will delete file data sitting in the backlog, for deleted devices
- Verify how many devices need to be pruned from the backlog by running the SQL script below:
use das
set nocount on
go
select GetDate() as 'Start Time'
-------- LOCAL VARIABLES ---------
DECLARE @hosts table(host_id int not null, cnt int)
INSERT INTO @hosts(host_id,cnt)
SELECT t.[host_id], COUNT(1)
FROM [dbo].[temp_antibody_instances] t WITH (NOLOCK)
JOIN [dbo].[host_state] hs WITH (NOLOCK) on hs.host_id= t.host_id
JOIN [dbo].[hostmain] hm WITH (NOLOCK) on hm.host_id = t.host_id
WHERE hs.defcon_id= 80 or hm.deleted = 1
GROUP BY t.[host_id]
SELECT TOP 100 t.[host_id],t.cnt 'Count of Temp AB records'
FROM @hosts t
ORDER BY t.cnt DESC
select GetDate() as 'End Time'
- Take a full backup of the DAS database
- Run the script below to remove all the temp antibodies for deleted devices:
use das
set nocount on
-- --------------------------------------------------------------------------------------------
-- Description:
-- 1. Deleted clones issue (backlog)
-- Delete backlog in 10,000 chunks
-- --------------------------------------------------------------------------------------------
-- Step 1: declare variables
declare @cnt int, @del_cnt int;
declare @max int;
declare @tbl table (id int identity, host_id int)
declare @chunkSize int
set @chunkSize=10000
-- Step 2: get list of hosts to correct
;WITH CTE AS
(
SELECT COUNT(1) [cnt], t.[host_id]
FROM [dbo].[temp_antibody_instances] t WITH (NOLOCK)
JOIN [dbo].[host_state] hs WITH (NOLOCK) on hs.host_id= t.host_id
JOIN [dbo].[hostmain] hm WITH (NOLOCK) on hm.host_id = t.host_id
WHERE hs.defcon_id= 80 or hm.deleted = 1
GROUP BY t.[host_id]
)
INSERT INTO @tbl (host_id)
SELECT host_id FROM CTE t
WHERE t.cnt>0
ORDER BY t.cnt DESC
SELECT @max = max(id), @cnt= 1 from @tbl ;
-- Step 3: loop; delete backlog for each host
while (@cnt <= @max)
begin
declare @hostID int;
select @hostID = host_id from @tbl where id = @cnt;
print convert(varchar(3), @cnt) + ' host id: ' + convert(varchar(30), @hostID);
-- Remove temp ab records for this host
--delete from dbo.temp_antibody_instances where host_id = @hostID
SET ROWCOUNT @chunkSize
WHILE (1=1)
BEGIN
delete from dbo.temp_antibody_instances where host_id = @hostID
SET @del_cnt = @@ROWCOUNT
IF @del_cnt=0
BREAK
END
SET ROWCOUNT 0
-- Update counter
set @cnt = @cnt + 1;
end
Related Content
