Finding and reducing TFS 2013 database size

Before I do an upgrade I always ask for the size of the TFS database set.  It provides me the information on how long an upgrade might take. The queries below, also provides information of attachments that might be taking up extra disk space.  By reducing the overall database set, the whole upgrade process will go faster. Plus, it is a good time to do some house cleaning.

Gather the database size information

To get the database sizes, you can run the following query

source of the queries: Guide to reduce TFS database growth using the Test Attachment Cleaner  tested on TFS 2013 RTM
use master

select DB_NAME(database_id) AS DBName, (size/128) SizeInMB

 FROM sys.master_files 

 where type=0  and substring(db_name(database_id),1,4)='Tfs_' and DB_NAME(database_id)<>'Tfs_Configuration' order by size desc 

GO

This will produce the database size in MB
image

To find out the attachments that are taking up space, you can run this set of queries
Find out which team projects have possibly too large attachments

use Tfs_DefaultCollection  --- Change to Collection Name

GO

select  p.projectname, sum(a.uncompressedlength)/1024/1024 as sizeInMBUncompressed, SUM(f.compressedlength)/1024/1024 as sizeInMBCompressed from dbo.tbl_Attachment as a 

inner join tbl_File as f on a.TfsFileId=f.FileId

inner join tbl_testrun as tr on a.testrunid=tr.testrunid 

inner join tbl_project as p on p.projectid=tr.projectid

group by p.projectname

order by sum(f.compressedlength) desc

Check which attachment types take up the most space

use Tfs_DefaultCollection 

select  a.attachmenttype, sum(f.compressedlength)/1024/1024 as sizeInMB from dbo.tbl_Attachment as a 

inner join tbl_File as f on a.TfsFileId=f.FileId

inner join tbl_testrun as tr on a.testrunid=tr.testrunid 

inner join tbl_project as p on p.projectid=tr.projectid

group by a.attachmenttype

order by sum(f.compressedlength) desc

Check which file types, by their extension, takes up the most space

use Tfs_DefaultCollection 

select  SUBSTRING(a.filename,len(a.filename)-CHARINDEX('.',REVERSE(a.filename))+2,999)as Extension, sum(f.compressedlength)/1024 as SizeInKB from tbl_Attachment as a 

inner join tbl_File as f on a.TfsFileId=f.fileid

group by SUBSTRING(a.filename,len(a.filename)-CHARINDEX('.',REVERSE(a.filename))+2,999)

order by sum(f.compressedlength) desc

These three queries can be ran together

image

To get an month range of when the attachment have been add you can run the following query

source of the queries: TFS2010: Test Attachment Cleaner and why you should be using it  tested on TFS 2013 RTM
USE Tfs_DefaultCollection 

SELECT

DATEADD(month,DATEDIFF(month,0,creationdate),0) as [Month],

SUM(UncompressedLength) / 1024 / 1024 as AttachmentSizeMB

FROM tbl_Attachment WITH (nolock)

GROUP BY DATEADD(month,DATEDIFF(month,0,creationdate),0)

ORDER BY DATEADD(month,DATEDIFF(month,0,creationdate),0)

image

To get more detail information on the tables space, you can run the following query

USE Tfs_DefaultCollection

CREATE TABLE #t 

( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), 

data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

GO

INSERT #t

EXEC [sys].[sp_MSforeachtable] 'EXEC sp_spaceused "?"'

GO

SELECT

name as TableName,

Rows,

ROUND(CAST(REPLACE(reserved, ' KB', '') as float) / 1024,2) as ReservedMB,

ROUND(CAST(REPLACE(data, ' KB', '') as float) / 1024,2) as DataMB,

ROUND(CAST(REPLACE(index_size, ' KB', '') as float) / 1024,2) as IndexMB,

ROUND(CAST(REPLACE(unused, ' KB', '') as float) / 1024,2) as UnusedMB

FROM #t

ORDER BY CAST(REPLACE(reserved, ' KB','') as float) DESC

GO

Drop table #t

This give a output of tables information in the database.

image

 

Reducing the database size

Identifying the issues is always the first steps. What to do to reduce the size of the database will take a few actions.  If you have a small manageable database size like my examples above, then no actions would be required.  But if you need to reduce the size here are a few tools to help.

Warning: Removing any files should be done with caution and NEVER DIRECTLY FROM THE DATABASE. As always before you modify, update or delete, take a full backup of the databases

image

 

After the removal of old and unused artifacts in the TFS database it is good to re-index and reduce the database size using SQL commands. 

Warning: These actions should be done off hours to reduce any impact to the teams.

More info on DBCC DBREINDEX (Transact-SQL) and DBCC SHRINKDATABASE (Transact-SQL)

USE [USE Tfs_DefaultCollection]

DBCC SHRINKFILE (N'Tfs_Tfs_DefaultCollection', 0, TRUNCATEONLY)

GO

DBCC SHRINKFILE (N'Tfs_NWC_Tfs_DefaultCollection_log' , 0, TRUNCATEONLY)

GO

DBCC SHRINKDATABASE(N'Tfs_Tfs_DefaultCollection' )

GO

EXEC sp_MSforeachtable @command1="print '?' 

DBCC DBREINDEX ('?', ' ', 80)"

 GO

EXEC sp_updatestats

Conclusion

Keeping up on the TFS database growth is not a everyday administrative task. However, before doing an upgrade, it is a great time to  clean out some of the old artifacts that are causing database bloating.   It also help in reducing the upgrade time if you do have a large database. 

I also have to give credit to the following site for their SQL code and information on reducing the TFS database.  I have tested all the script on a TFS 2013 instance and verified that the scripts do worked

http://geekswithblogs.net/terje/archive/2011/11/15/guide-to-reduce-tfs-database-growth-using-the-test-attachment.aspx

http://blogs.msdn.com/b/granth/archive/2011/02/12/tfs2010-test-attachment-cleaner-and-why-you-should-be-using-it.aspx

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s