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
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.
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
One more advice I found when faced with a similar problem is to prevent the LDF file from growing back by changing the recovery mode of the table to 'simple'.
First of all, shrink the LDF file. I did it using the SQL management studio.
After shrinking the DB and making sure the TFS still works, follow the instructions here: stop-sql-server-transaction-log-ldf-files-from-growing-indefinitely.
This will cause the SQL server to reduce the amount of recovery data that is being saved to the transaction log so that you won't need to repeat the shrink operation every X month.
