8

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
LockTar
  • 5,364
  • 3
  • 46
  • 72
  • 1
    Try running the SQL query from this SO question and see what is taking up all the space then update your question: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database – Dylan Smith Oct 24 '13 at 06:59
  • I edited the question with the query result as you asked. – LockTar Oct 24 '13 at 10:01
  • That shows the largest table as 41MB, how big is your DB? – Dylan Smith Oct 24 '13 at 10:45
  • Around the 160GB. When I saw the results of the scripts I also thought this couldn't be right. I checked the name of the mdf file and it is the right one. So is the query wrong? I used the one in your link that was marked as answer. – LockTar Oct 24 '13 at 12:02

3 Answers3

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.

Szymon
  • 42,577
  • 16
  • 96
  • 114
  • You mean the ldf file of the database? My ldf file of the DefaultCollection is 32.1 GB and the mdf file is 130 GB. – LockTar Oct 31 '13 at 07:33
  • Yes, that's what I meant. – Szymon Oct 31 '13 at 10:00
  • Okay... I searched for shrinking the ldf file and see this article. http://technet.microsoft.com/en-us/library/ms365418.aspx. But when do I need to do it? When do I know it's time? How do I see that there is unused space? – LockTar Oct 31 '13 at 12:09
1

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
galsi
  • 421
  • 1
  • 6
  • 19
0

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.

OSH
  • 2,847
  • 3
  • 25
  • 46