How to remove stale DDC from XenDesktop 5 Farm

Stale DDC inside XenDesk 5 Farm may get created because of various reason and this does not get removed unless database is cleaned.

1. If you run Get-brokercontroller from PS console of Desktop Studio then you see the stale entry. Below two stale entry for SID

S-1-5-21-1757981266-413027322-725345543-64627 and  other one ending with 64712 

2. Same thing will be shown via Desktop Studio

 

3. Following script needs to be run in same order from desktop studio PS console with XD database name

Get-ConfigDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid > EvictDDC.sql  (Here we are creating EvictDDC.sql)
Get-AcctDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid >> EvictDDC.sql(Here we are appending to  EvictDDC.sql)
Get-HypDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid >> EvictDDC.sql
Get-ProvDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid >> EvictDDC.sql
Get-PvsVmDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid >> EvictDDC.sql
Get-BrokerDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid >> EvictDDC.sql

4. This is how Script needs to be run in following order . First SID variable needs to be set and then rest of the commands need to be run. Here we are creating EvictDDC.sql file which is nothing but a stored procedure

5.  This file can be found under following

6. Now when we open this file with notepad  following entry can be found.

 

7.   Open SQL server management studio and select the database and then open new script. Select the content from EvictDDC.sql  and paste as shown. Execute as shown below

8.   Last command Get-BrokerDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid >> EvictDDC.sql  need to run as follows.

9.  Sometime Get-BrokerDBSchema -DatabaseName <XD database name> -ScriptType Evict -SID $sid >> EvictDDC.sql will fail with following message. Create new store procedure using working XD SID and then replace host name and SID with non working SID.

10.  Once all the above store procedure ran it will remove the stale entry from DDC

Even Desktop Studio will update the entry