TFS releases cleanup

releasemanagement_cleanup.sql

/* 
 This script is carries absolutely no guarantee and running it might cause your database 
 to end up in an unexpected state. 
 Run it completely at your own risk.
*/

/*
declare @releaseId int
set @releaseId = 2 -- Enter the id of the release to delete here
*/

declare @releasecutoff varchar(20)
set @releasecutoff = '01/01/2016'

/*	Remove notifications	*/
delete n from  
	[dbo].[Notification] n
	join release r
	on n.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Remove deployment queue entries		*/
delete a from [dbo].DeploymentQueue as a 
join ReleaseStageActivity as b on a.ReleaseStageActivityId = b.Id 
join ReleaseComponent as c on b.ReleaseComponentId = c.Id  
join release r
	on c.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Prep to remove resources				*/
DECLARE @ResourcesToRemove TABLE
(
  PartitionId int, 
  Id int
)

insert into @ResourcesToRemove 
select re.PartitionId, re.id from [Resource] re
join [dbo].[ComponentDeploymentLog] cl
	/* The bulk of our resources seem associated with AutoInstallLogResourceId, 
	but some are also associated with CustomInstallLogResourceId. */
	on cl.AutoInstallLogResourceId = re.Id
join ReleaseComponent rc 
	on cl.ReleaseComponentId = rc.Id 
join release r
	on rc.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)
	and 
	re.Id > 20 /* Be careful -- Resources 0-20 seem like they're special.  Don't remove them. */

/*	Remove component deployment log		*/
delete a from [dbo].[ComponentDeploymentLog] as a 
join ReleaseComponent as b on 
	a.ReleaseComponentId = b.Id 
join release r
on b.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Remove resources (this is the big one)	*/
delete re from [Resource] re
join @ResourcesToRemove rr 
	on rr.PartitionId = re.PartitionId and rr.Id = re.Id

/*	Remove release configuration variables	*/
delete a from [dbo].[ReleaseConfigurationVariable] as a 
join [ReleaseStageActivity] as b on a.ReleaseStageActivityId = b.Id
join ReleaseComponent as c on b.ReleaseComponentId = c.Id  
join release r
	on c.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Remove release stage activities	*/
delete a from [dbo].ReleaseStageActivity as a 
join ReleaseComponent as b on 
a.ReleaseComponentId = b.Id 
join release r
	on b.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Remove release components	*/
delete rc from [dbo].[ReleaseComponent] rc
join release r
	on rc.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Remove the deployment log entries	*/
delete a from [dbo].[DeploymentLog] as a 
join ReleaseStep as b on a.ReleaseStepId = b.Id 
join release r
	on b.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Remove release steps	*/
delete rs from  [dbo].[ReleaseStep] rs
join release r
	on rs.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/* Remove the release stage workflow entries	*/
delete rsw from  [dbo].[ReleaseStageWorkflow] rsw
join release r
	on rsw.ReleaseId = r.Id
where 
	r.CreatedOn <= CONVERT(DATETIME, @releasecutoff)

/*	Finally, remove the release		*/
delete from  [dbo].[Release]
where CreatedOn <= CONVERT(DATETIME, @releasecutoff)

So after doing some investigating (and using this script to find the largest tables) it looks like the bulk of the space is being taken up by the Resource table.

I'm going to do my best to add that table to this cleanup.

Looking at the foreign keys on the Resource table, It looks like the rows should be removed if they are related to ComponentDeploymentLog -- either CustomInstallLogResourceId or AutoInstallLogResourceId... but NOT if they are in the (very small) table DeployerToolResource

Updated the script to remove the most common type of resources (for us): AutoInstallLogResourceId