TFS Storage usage

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

Author: KimC

TFS admin and deployment fellow

3 thoughts on “TFS Storage usage”

  1. 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

    Like

    1. 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

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s