TFS Database Server Transaction Log File Size

So, we had an issue where the TFS2012 server ran out of space on it's data drive.  On investigation we found the database for our main team project was around 11Gb in size but had a 200Gb log file that was using the majority of the disc (it's a modestly specced hyperv server!).

After some debate/research we decided to simply drop the log file and found that TFS did not suffer any problems by doing so - if you're going to do this yourself then TAKE A BACKUP first, I can't guarantee this is a safe process.

Don't forget to replace all the "Tfs_MyProj" with your project names.

--BOOT ALL SESSIONS OFF THE DATABASE TO ALLOW IT TO BE SET TO SIMPLE RECOVERY

DECLARE @sessions TABLE([spid] FLOAT,[ecid] FLOAT,[status] VARCHAR(1000),[loginame] VARCHAR(1000),[hostname] VARCHAR(1000),[blk] FLOAT,[dbname] VARCHAR(1000),[cmd] VARCHAR(1000),[request_id] FLOAT)

INSERT INTO @sessions EXEC sp_who

DECLARE @spid int

DECLARE db_cursor CURSOR FOR SELECT spid FROM @sessions WHERE dbname = 'Tfs_MyProj' and spid <> @@SPID

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @spid 

WHILE @@FETCH_STATUS = 0 

BEGIN 

      DECLARE @killStatement varchar(100)

      SELECT @killStatement = 'kill '+CONVERT(VARCHAR, @spid)

      EXEC (@killStatement)

      FETCH NEXT FROM db_cursor INTO @spid 

END 

CLOSE db_cursor 

DEALLOCATE db_cursor

GO

--TRUNCATE AND DROP TRANSACTION LOGS

ALTER DATABASE Tfs_MyProj SET RECOVERY SIMPLE;

USE Tfs_MyProj;

DBCC SHRINKFILE (Tfs_MyProj , 64);

ALTER DATABASE Tfs_MyProj SET RECOVERY FULL;

GO