Playing with BP 5.x and queue item data dump

Hi guys, this is just a brief post about queue items dumping.
I found this very useful with BP version 5.x and high-intensive volume data which caused failure in GetNextItem action.
So i need to dump data to another table in order to keep DB as small as possible waiting for the permission from the customer, to wipe all the old data (or restoring just a part of the amount of data).

The SQL script is self explaining and deals with ID consistency, nothing hard to do, but if you're asking if you can do this operation without corrupting the DB, yes you can, so have fun:

-- We need to create just one table to dump data, otherwise you can use SELECT * INTO TABLE which creates a new table everytime
DROP TABLE [dbo].[BPAWorkQueueItem_TEMP]
CREATE TABLE [dbo].[BPAWorkQueueItem_TEMP](
 [id] [uniqueidentifier] NOT NULL,
 [queueid] [uniqueidentifier] NOT NULL,
 [keyvalue] [nvarchar](255) NULL,
 [status] [nvarchar](255) NULL,
 [attempt] [int] NULL,
 [loaded] [datetime] NULL,
 [completed] [datetime] NULL,
 [exception] [datetime] NULL,
 [exceptionreason] [nvarchar](max) NULL,
 [deferred] [datetime] NULL,
 [worktime] [int] NULL,
 [data] [nvarchar](max) NULL,
 [queueident] [int] NOT NULL,
 [ident] [bigint] IDENTITY(1,1) NOT NULL,
 [sessionid] [uniqueidentifier] NULL,
 [priority] [int] NOT NULL,
 [prevworktime] [int] NOT NULL,
 [attemptworktime]  AS ([worktime]-[prevworktime]) PERSISTED,
 [finished]  AS (isnull([exception],[completed])) PERSISTED,
 [exceptionreasonvarchar]  AS (CONVERT([nvarchar](400),[exceptionreason])),
 [exceptionreasontag]  AS (CONVERT([nvarchar](415),N'Exception: '+replace(CONVERT([nvarchar](400),[exceptionreason]),N';',N':'))) PERSISTED,
 [encryptid] [int] NULL,
 [lastupdated]  AS (coalesce([completed],[exception],[loaded])) PERSISTED,
 [locktime] [datetime] NULL,
 [lockid] [uniqueidentifier] NULL)
GO

-- We enable identity insert, this will preserve ID assigned by BluePrism, then we dump data
-- in the where condition we use queue name and completed date (so we do not take any pending or deferred item)
SET IDENTITY_INSERT [dbo].[BPAWorkQueueItem_TEMP] ON

INSERT INTO [dbo].[BPAWorkQueueItem_TEMP](
 [id]
 ,[queueid]
 ,[keyvalue]
 ,[status]
 ,[attempt]
 ,[loaded]
 ,[completed]
 ,[exception]
 ,[exceptionreason]
 ,[deferred]
 ,[worktime]
 ,[data]
 ,[queueident]
 ,[ident]
 ,[sessionid]
 ,[priority]
 ,[prevworktime]
 ,[locktime]
 ,[lockid])
 SELECT wqi.[id]
    ,wqi.[queueid]
    ,wqi.[keyvalue]
    ,wqi.[status]
    ,wqi.[attempt]
    ,wqi.[loaded]
    ,wqi.[completed]
    ,wqi.[exception]
    ,wqi.[exceptionreason]
    ,wqi.[deferred]
    ,wqi.[worktime]
    ,wqi.[data]
    ,wqi.[queueident]
    ,wqi.[ident]
    ,wqi.[sessionid]
    ,wqi.[priority]
    ,wqi.[prevworktime]
    ,wqi.[locktime]
    ,wqi.[lockid]

   FROM [BluePrism].[dbo].[BPAWorkQueueItem] as wqi
   JOIN [BluePrism].[dbo].[BPAWorkQueue] as wq on wqi.queueid = wq.id
   WHERE 
  wq.name = 'Queue.BonificiEsteroEntrata.ContiAttesaLavorazione' AND 
  finished < DATEADD(MONTH, -3, GETDATE())
GO

-- Here we restore identity insert value
SET IDENTITY_INSERT [dbo].[BPAWorkQueueItem_TEMP] OFF

-- Finally we erase data from original table
DELETE FROM [dbo].[BPAWorkQueueItem] WHERE 
 EXISTS (SELECT * FROM [dbo].[BPAWorkQueueItem_TEMP] WHERE [BPAWorkQueueItem].ident = [BPAWorkQueueItem_TEMP].ident)

Read More