This is the first post on monitoring storage usage of on-premise TFS.
Often the databases behind TFS is a black boks, where storage just grows, so we will dig into the database behind to see where the bits and bytes goes.
First we can have a look at the growth of the content table, where most of the content goes. It is also the table to watch as it can grows very fast.
Select DATEPART(yyyy, CreationDate) as [year],
DATEPART(mm, CreationDate) as [month],
SUM(DATALENGTH(Content)) / 1048576 as [Size in Mb]
From tbl_Content With (nolock)
Group by DATEPART(yyyy, CreationDate),
DATEPART(mm, CreationDate)
Order by DATEPART(yyyy, CreationDate),
DATEPART(mm, CreationDate)
This will show the growth of the content table, per month.
Updated with nolock hint
ALMGuide 
Hi Kim,
let’s for the fun of it say, that we have 15.678.049 rows in our tbl_content.
Yeah, I know it seems oddly specific 🙂
But do you have any optimized query that won’t take 30+ minutes to hark through the table while hogging all the sql servers resources? 🙂
And thank you for the presentation yesterday at the community
LikeLike
Just a random number…
I have updated the query with the nolock hint. This will optimize the reading, and as we don’t need 100% accurate results, we can use this. If it stills put you SQL server on overdue, then look at its resource and recalculate the statistics.
Kim
LikeLike