How to shrink TFS database size

Question

We have a TFS2010 environment. The size is growing every week for a long time now.

We deleted a lot of old branches and team projects. We also used the test attachment cleaner for several projects like Brian Harry said in his post. http://blogs.msdn.com/b/bharry/archive/2011/10/31/tfs-databases-growing-out-of-control.aspx

The database isn't getting any smaller. I also tried to use the destroy command for several times but nothing is helping.

I checked every log I could think of but can't find any error about it.

Anyone a suggestion?

Thanks

Edit with result of the query that is asked in the comments:

TableName                       SchemaName RowCounts TotalSpaceKB UsedSpaceKB UnusedSpaceKB
FieldsDataArchive               dbo        0         0            0           0
tbl_AuditLog                    dbo        41710     5168         3800        1368
tbl_AuthorizationUpdateLock     dbo        1         16           16          0
tbl_BuildOutput                 dbo        0         0            0           0
tbl_BuildServerProperties       dbo        1         16           16          0
tbl_BuildSqlNotification        dbo        124445    8432         6544        1888
tbl_Counter                     dbo        3         16           16          0
tbl_LastChangeId                dbo        1         16           16          0
tbl_Replication                 dbo        1         16           16          0
tbl_Repository                  dbo        1         16           16          0
TempADObjectMemberships         dbo        0         0            0           0
TempADObjects                   dbo        0         0            0           0
Templates                       dbo        7         41328        41280       48

Answer 1

Most of the disk space in your case will be used by the transaction log file, not the data file.

The query above shows only the disk space used by the data file.

You may look at shrinking the transaction log if it has available space.

Answer 2

After viewing unreasonable growth of the transaction log of the TFS database I couldn’t find the exact cause of the growth and couldn’t also control automatically shrinking of the log.

Similar solution without the full database backup

I tried this script several days on non-production server and only after i switched to production server.

Ruining on SQL server 2012 & TFS 2015

the following script automatically shrink the transaction log.

Run this script after full back up using the SQL job

Scripts parts:

1) Disconnect all connection to specific database

2) Switch Backup model to simple

3) Set database log size to unlimited

4) Shrink log file to 200mb (or any size you wish)

5) Set max size to 50000 (or any size you wish)

6) Set Backup model to full

Script run takes about 3 -5 seconds on a 140GB DB

USE [master]
GO
ALTER DATABASE [Tfs] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [Tfs] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [Tfs] MODIFY FILE ( NAME = N'Tfs_log', MAXSIZE = UNLIMITED)
GO

USE [Tfs]
GO
DBCC SHRINKFILE (N'Tfs_log' , 200)
GO

ALTER DATABASE [Tfs] MODIFY FILE ( NAME = N'Tfs_log', MAXSIZE = 50000)

USE [master]
GO
ALTER DATABASE [Tfs] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [Tfs] SET MULTI_USER
GO