cmsPropertyData-Umbraco-Clean.sql

--Script for umbraco database, useful if you are having media/file problems
--This will remove duplicated null entries in the cmsPropertyData table
--Clean Up Content Properties for Media (Id=6) and Files (Id=24)

--Work through these id's one by one
  DECLARE @propertyTypeId INT;
  --File
  SET @propertyTypeId = 24 --umbracoFile
  --SET @propertyTypeId = 25 --umbracoExtension
  --SET @propertyTypeId = 26 --umbracoBytes
  --SET @propertyTypeId = 180 -- title
  --SET @propertyTypeId = 189 -- caption
  --Media
  --SET @propertyTypeId = 6 --umbracoFile
  --SET @propertyTypeId = 7 --umbracoWidth
  --SET @propertyTypeId = 8 --umbracoHeight
  --SET @propertyTypeId = 9 --umbracoBytes
  --SET @propertyTypeId = 10 --umbracoExtension
  --SET @propertyTypeId = 48 -- caption

DELETE FROM cmsPropertyData WHERE Id IN --uncomment to delete records
(
	--Get the damaged records version and node id
	SELECT 
		--[versionId], contentNodeId, COUNT(*) AS Number, Max(Id) as IdToKeep,--uncomment this line to see details of broken records
		Min(id) AS IdToDelete
	FROM [cmsPropertyData]
	WHERE 
		Propertytypeid  = @propertyTypeId
		AND versionId IN 
		(
			SELECT 
				versionId 
			FROM cmsPropertyData
			WHERE (dataNtext IS NULL AND dataDate IS NULL AND dataDecimal IS NULL AND dataInt IS NULL AND dataNvarchar IS NULL)
			AND propertytypeid = @propertyTypeId
		)
	GROUP BY versionId, contentNodeId
	HAVING COUNT(*) > 1
 )


  

Hey, no tickets that I am aware of, and not certain what causes the issue, Its possible that its due to a failed umbraco upgrade, but I'm not 100% certain. It was a nightmare to track down what was going on with the site that lead to this script being written.