Skip to content

Instantly share code, notes, and snippets.

@michaellwest
Created September 24, 2019 16:03
Show Gist options
  • Save michaellwest/51f4bcaf93400649b5482cf061d0e869 to your computer and use it in GitHub Desktop.
Save michaellwest/51f4bcaf93400649b5482cf061d0e869 to your computer and use it in GitHub Desktop.
Cleanup orphaned blob data using SSMS. When Sitecore runs this process it may timeout otherwise.
DECLARE @BlobID uniqueidentifier;
SELECT @BlobID = '{FF8A2D01-8A77-4F1B-A966-65806993CD31}';
WITH [BlobFields] ([fieldid])
AS (SELECT [sharedfields].[itemid]
FROM [sharedfields]
WHERE [sharedfields].[fieldid] = @BlobID
AND [sharedfields].[value] = 1
UNION
SELECT [versionedfields].[itemid]
FROM [versionedfields]
WHERE [versionedfields].[fieldid] = @BlobID
AND [versionedfields].[value] = 1
UNION
SELECT [unversionedfields].[itemid]
FROM [unversionedfields]
WHERE [unversionedfields].[fieldid] = @BlobID
AND [unversionedfields].[value] = 1
UNION
SELECT [archivedfields].[archivalid]
FROM [archivedfields]
WHERE [archivedfields].[fieldid] = @BlobID
AND [archivedfields].[value] = 1),
[ExistingBlobs] ([blobid])
AS (SELECT [blobs].[blobid]
FROM [blobs]
JOIN [sharedfields]
ON '{'
+ CONVERT(NVARCHAR(max), [blobs].[blobid])
+ '}' = [sharedfields].[value]
JOIN [BlobFields]
ON [sharedfields].[fieldid] = [blobfields].[fieldid]
UNION
SELECT [blobs].[blobid]
FROM [blobs]
JOIN [sharedfields]
ON CONVERT(NVARCHAR(max), [blobs].[blobid]) =
[sharedfields].[value]
JOIN [BlobFields]
ON [sharedfields].[fieldid] = [blobfields].[fieldid]
UNION
SELECT [blobs].[blobid]
FROM [blobs]
JOIN [versionedfields]
ON '{'
+ CONVERT(NVARCHAR(max), [blobs].[blobid])
+ '}' = [versionedfields].[value]
JOIN [BlobFields]
ON [versionedfields].[fieldid] = [blobfields].[fieldid]
UNION
SELECT [blobs].[blobid]
FROM [blobs]
JOIN [versionedfields]
ON CONVERT(NVARCHAR(max), [blobs].[blobid]) =
[versionedfields].[value]
JOIN [BlobFields]
ON [versionedfields].[fieldid] = [blobfields].[fieldid]
UNION
SELECT [blobs].[blobid]
FROM [blobs]
JOIN [unversionedfields]
ON '{'
+ CONVERT(NVARCHAR(max), [blobs].[blobid])
+ '}' = [unversionedfields].[value]
JOIN [BlobFields]
ON [unversionedfields].[fieldid] = [blobfields].[fieldid]
UNION
SELECT [blobs].[blobid]
FROM [blobs]
JOIN [unversionedfields]
ON CONVERT(NVARCHAR(max), [blobs].[blobid]) =
[unversionedfields].[value]
JOIN [BlobFields]
ON [unversionedfields].[fieldid] = [blobfields].[fieldid]
UNION
SELECT [blobs].[blobid]
FROM [blobs]
JOIN [archivedfields]
ON '{'
+ CONVERT(NVARCHAR(max), [blobs].[blobid])
+ '}' = [archivedfields].[value]
JOIN [BlobFields]
ON [archivedfields].[fieldid] = [blobfields].[fieldid]
UNION
SELECT [blobs].[blobid]
FROM [blobs]
JOIN [archivedfields]
ON CONVERT(NVARCHAR(max), [blobs].[blobid]) =
[archivedfields].[value]
JOIN [BlobFields]
ON [archivedfields].[fieldid] = [blobfields].[fieldid])
DELETE FROM [blobs]
WHERE NOT EXISTS (SELECT NULL
FROM [ExistingBlobs]
WHERE [existingblobs].[blobid] = [blobs].[blobid])
Import-Function -Name Invoke-SqlCommand
$database = Get-Database -Name "master"
$connection = [Sitecore.Configuration.Settings]::GetConnectionString($database.Name)
$query = @"
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalPageSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedPageSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
"@
Invoke-SqlCommand -Connection $connection -Query $query | Show-ListView
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment