TFS 2015 CodeSense Cleanup

Question

I recently inherited the responsibility of administering a TFS instance but I have no previous experience. The database for the Team Project seems to have grown abnormally large during the last 6 months, and reading up on everything I can find has helped me (I think) to identify the culprit, but I don't know what to do. Any help would be appreciated.

I've run the queries that are widely available such as this one:

SELECT TOP 3 o.name, 
SUM(reserved_page_count) * 8.0 / 1024 SizeInMB,
SUM(CASE 
WHEN p.index_id <= 1 THEN p.row_count
ELSE 0
END) Row_Count
FROM sys.dm_db_partition_stats p
JOIN sys.objects o
ON p.object_id = o.object_id
GROUP BY o.name
ORDER BY SUM(reserved_page_count) DESC

To find this:

name             SizeInMB       Row_Count
tbl_Content      313489.765625  10090278
tbl_Version      33400.828125   27518951
tbl_AggregateMap 10638.539062   32955145

And this other query too:

SELECT Owner = 
CASE
WHEN OwnerId = 0 THEN 'Generic' 
WHEN OwnerId = 1 THEN 'VersionControl'
WHEN OwnerId = 2 THEN 'WorkItemTracking'
WHEN OwnerId = 3 THEN 'TeamBuild'
WHEN OwnerId = 4 THEN 'TeamTest'
WHEN OwnerId = 5 THEN 'Servicing'
WHEN OwnerId = 6 THEN 'UnitTest'
WHEN OwnerId = 7 THEN 'WebAccess'
WHEN OwnerId = 8 THEN 'ProcessTemplate'
WHEN OwnerId = 9 THEN 'StrongBox'
WHEN OwnerId = 10 THEN 'FileContainer'
WHEN OwnerId = 11 THEN 'CodeSense'
WHEN OwnerId = 12 THEN 'Profile'
WHEN OwnerId = 13 THEN 'Aad'
WHEN OwnerId = 14 THEN 'Gallery'
WHEN OwnerId = 15 THEN 'BlobStore'
WHEN OwnerId = 255 THEN 'PendingDeletion'
END,
SUM(CompressedLength) / 1024.0 / 1024.0 AS BlobSizeInMB
FROM tbl_FileReference AS r
JOIN tbl_FileMetadata AS m
ON r.ResourceId = m.ResourceId
AND r.PartitionId = m.PartitionId
WHERE r.PartitionId = 1
GROUP BY OwnerId
ORDER BY 2 DESC

To find

Owner           BlobSizeInMB
CodeSense       264426.749071121093
VersionControl  8728.462930678710
TeamTest        477.505887984375
ProcessTemplate 2.953623771484
FileContainer   0.024445533203

And while VersionControl = 8GB seems completely ok given our code, CodeSense is crazy large. I have not found info about that feature anywhere, or how to disable it. Please help!

PS: If it's related to the CodeLens feature in VS, we're not using it either.

Answer 1

The feature is called CodeIndex, and that's why I could not find it earlier.

Here's all the info required to configure it: https://docs.microsoft.com/en-us/visualstudio/ide/codeindex-command?view=vs-2015

I turned it off and I am now trying to destroy the index, but it errors out with

TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct.

But that's another issue...

EDIT: This is what's happened. I checked the event viewer on the app tier and found that this was what was timing out:

EXEC CodeSense.prc_DeleteAggregates @partitionId=1

I inspected the SP, and it was doing 3 things

• Call prc_iPrepareExecution which does nothing: RETURN 0
• Delete from [CodeSense].[tbl_AggregatorInputQueue] where @partitionId is 1. The table was empty so nothing to do there.
• Delete from [CodeSense].[tbl_AggregateMap] where @partitionId is 1. I queried the DB and found out that there were no rows with any other partition ids. Furthermore, a SELECT COUNT(*) was taking over 5 minutes to complete, so I cancelled it and then it dawned on me: I could simply truncate the table because the only partitionId in my case is 1. This saved me from clogging the disk with lots of useless transaction logs and deleting stuff in batches.

Sure enough, I truncated it, but out of good measure i then reran TFSConfig CodeIndex /destroyCodeIndex on my collection and this time it worked.

However, when I went back to the db tier to recover my now-presumably-empty space: it wasn't free yet.

I went back to the event log and found EXEC CodeSense.prc_DeleteOrphanedFiles @partitionId=1,@createdBefore=03/17/2019 21:05:28 was timing out this time!

This SP is creating a table in memory of things to delete and then deletes them. I created a copy of this SP with a TOP 100000 clause to limit the amount of rows being deleted at a time, and ran it several times until it got rid of the 2M+ rows.

However, something else, at some point, must be responsible for cleaning up the tables tbl_FileReference, tbl_FileMetadata and especially tbl_Content.

I found a blog post that suggested running EXEC prc_CleanupDeletedFileContent 1 once, followed by EXEC prc_DeleteUnusedFiles 1, 0, 100000 serveral times.

After 25 minutes, the CodeSense blob is completely gone

Owner           BlobSizeInMB
VersionControl  8728.347916602539
TeamTest        477.505887984375
ProcessTemplate 2.953623771484
FileContainer   0.024445533203

However, tbl_Content is still huge and the query is still running

I am going to wait for a day or so to see if things improve or if I have to keep digging.

EDIT 2: Over 24 hours have passed and the query is still running. Running the diagnosis query tells me that tbl_Content is indeed shrinking, and when I use the "Shrink files" option in SQL Management Studio, the data files are starting to have a lot more free space, so it's working!

Since the database log files are not growing and everything looks stable, I guess I'm just going to wait until the query completes its work, rerun it for good measure, and then proceed to recover the unused space at the database level.

Good luck if you're in this same situation.