Monitoring database grooming does not work and old data is left behind if the size of the Citrix Monitor Database Is too large.

The above mentioned sample code is provided to you as is with no representations, warranties or conditions of any kind. You may use, modify and distribute it at your own risk. CITRIX DISCLAIMS ALL WARRANTIES WHATSOEVER, EXPRESS, IMPLIED, WRITTEN, ORAL OR STATUTORY, INCLUDING WITHOUT LIMITATION WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NONINFRINGEMENT. Without limiting the generality of the foregoing, you acknowledge and agree that (a) the sample code may exhibit errors, design flaws or other problems, possibly resulting in loss of data or damage to property; (b) it may not be possible to make the sample code fully functional; and (c) Citrix may, without notice or liability to you, cease to make available the current version and/or any future versions of the sample code. In no event should the code be used to support ultra-hazardous activities, including but not limited to life support or blasting activities. NEITHER CITRIX NOR ITS AFFILIATES OR AGENTS WILL BE LIABLE, UNDER BREACH OF CONTRACT OR ANY OTHER THEORY OF LIABILITY, FOR ANY DAMAGES WHATSOEVER ARISING FROM USE OF THE SAMPLE CODE, INCLUDING WITHOUT LIMITATION DIRECT, SPECIAL, INCIDENTAL, PUNITIVE, CONSEQUENTIAL OR OTHER DAMAGES, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Although the copyright in the code belongs to Citrix, any distribution of the sample code should include only your own standard copyright attribution, and not that of Citrix. You agree to indemnify and defend Citrix against any and all claims arising from your use, modification or distribution of the sample code.

This issue has been addressed in XenApp/XenDesktop 7.15 LTSR CU1 under LC8770.
https://docs.citrix.com/en-us/xenapp-and-xendesktop/7-15-ltsr/whats-new/cumulative-update-1/fixed-issues.html

WORKAROUND

If the database is oversized, first step is to identify which table(s) are to blame.
Use the following SQL query to find out:
 
SELECT concat(s.name, '.', b.name) as XenDesktop_table, i.Rows
FROM sysobjects o
JOIN sysindexes i on o.id = i.id
join sys.objects b on o.id = b.object_id
  join sys.schemas s on s.schema_id = b .schema_id
WHERE o.type = 'U' AND i.IndId < 2
--and s.name = 'monitordata'
order by i.Rows desc


Next, delete the records in chunks from the oversized table, for ex: If MonitorData.Session table is oversized,  please use following query to delete the entries greater than 90 days manually.
This would delete records in chunks and doesn’t grow transaction logs. Here important thing, we need to run the delete query under transaction so that whatever shorter delete happens, it would commit it after that.
 
    DECLARE @Deleted_Rows INT
    SET  @Deleted_Rows = 1
    WHILE(@Deleted_Rows > 0)
    BEGIN
    BEGIN TRANSACTION;
    DELETE TOP (5000)  from [MonitorData].[Session]
    where ModifiedDate <= GetDate() -90
    and EndDate IS NOT NULL
    and ConnectionState = 3
    SET @Deleted_Rows = @@ROWCOUNT
 
   COMMIT TRANSACTION;
    END
 

---

Problem Cause

The existing grooming stored procedure did not delete the table record in chunks. If Monitor database table size is very huge, it increases the size of transaction logs. If in between the grooming operation times out, it does not commit the changes even for the tabled which have been groomed.
 

Monitoring Database Grooming Does Not Work If The Size Of The Monitor Database Is Too Large