How many times have you been caught trying to figure out what is the current size of your TFS databases? I find more often than not, just before a TFS Migration or a backup procedure, this is a very frequent ask. Here’s a nifty T-SQL Script to get your answer.
-- Please use judiciously as Microsoft does not recommend you directly hitting the DB
-- Determine the size of MS SQL Server Database
-- This is especially useful to determine how much space you need for backup/migration etc.
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + CHAR(13) + 'UNION ALL' ,'') + ' SELECT ''' + name + ''' AS DBNAME,' + 'sum(size * 8 /1024.0) AS MB from ' + QUOTENAME(name) + '.dbo.sysfiles'
FROM sys.databases WHERE name LIKE 'tfs_%'
ORDER BY name
Keep it handy for other systems why don’t ya 🙂