The Collection database of a client saw some explosive growth and I was asked to figure out where the storage had gone to.
You'll find a few SQL statements floating around that can help you with these types of questions, but these don't list the Package Feed data usage. After some spelunking in the my local server installation I cobbled together the following statement to dig a little bit deeper:
select
[f].FeedName,
sum(cast([list].BlockFileLength as decimal(38)))/1024.0/1024.0 AS SizeInMb
from
BlobStore.tbl_Blob [blob]
join BlobStore.tbl_Block [block] on [blob].BlobId = [block].BlobId
join BlobStore.tbl_BlockList [list] on [list].BlobId = [block].BlobId
join [Feed].[tbl_PackageVersionIndex] [fd] on '0x'+[fd].StorageId = CONVERT(varchar(max),blob.BlobId ,1)
join [Feed].[tbl_Feed] [f] on [fd].FeedId = [f].FeedId
join [Feed].[tbl_PackageIndex] [p] on [p].PackageId = [fd].PackageId
group by
[f].FeedName
order by
SizeInMb desc
select
[f].FeedName,
[p].PackageName,
sum(cast([list].BlockFileLength as decimal(38)))/1024.0/1024.0 AS SizeInMb,
count(distinct [pvi].PackageVersionId) as [Versions]
from
BlobStore.tbl_Blob [blob]
join BlobStore.tbl_Block [block] on [blob].BlobId = [block].BlobId
join BlobStore.tbl_BlockList [list] on [list].BlobId = [block].BlobId
join [Feed].[tbl_PackageVersionIndex] [fd] on '0x'+[fd].StorageId = CONVERT(varchar(max),blob.BlobId ,1)
join [Feed].[tbl_Feed] [f] on [fd].FeedId = [f].FeedId
join [Feed].[tbl_PackageIndex] [p] on [p].PackageId = [fd].PackageId
join [Feed].[tbl_PackageVersionIndex] [pvi] on [pvi].PackageId = [p].PackageId
group by
[f].FeedName, [p].PackageName
order by
SizeInMb desc
The outcome is a list of feeds and their total consumption as well as a list of feeds decomposed by the different packages in that feed.
From here further exploration should be a piece of cake!
Top comments (0)