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)

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