Cleaning up and reduce the size of the TFS database using TFS stored procedures

by XDK 7. September 2014 18:57

Explanation:

  • If your Files table is large:

EXEC prc_DeleteUnusedFiles 1, 0, 100

@partitionId = 1, @chunkSize=100

The nature of prc_deleteunusedfiles is that once it deletes a bunch of files, that causes more files to be able to be deleted due to our delta chains. This procedure is run on a weekly schedule so it will clean up the next set of files the next time the job runs.  This procudure can executed  repeatedly until FWFilesToDelete stops decreasing.This procedure may run for a long time, that’s why it has the third parameter which defines the batch size.

  • If your Content table is large:

EXEC prc_DeleteUnusedContent 1

@partitionId = 1

  • Queries to monitor the DB size

SELECT COUNT(*) FROM tbl_File f WHERE f.PartitionId = 1 AND f.OwnerId = 1 AND f.DeletedOn IS NULL AND f.CreationDate < DATEADD(DAY, -1, GETUTCDATE()) 

AND f.FileId NOT IN (SELECT FileId FROM tbl_Version WHERE PartitionId = 1 AND FileId IS NOT NULL)

 

SELECT COUNT(*) from tbl_File f WHERE f.DeletedOn IS NOT NULL

Tags:

Comments are closed

About the author

My name is Xavier Dilip Kumar Jayaraj having 16+ years of IT experience which includes solid experience and depth Knowledge in Application Life Cycle Management, Configuration Management, Implementation and Support using TFS on-premises and Azure DevOps. I have invested in gaining DevOps knowledege to expertise with Cloud Computing providers namely Microsoft Azure and Amazon Web Services in recent years. I am very positive to learn and adapt emerging technologies to client’s environment.

Microsoft Certified: Azure Administrator Associate

Microsoft Certified: Azure DevOps Engineer Expert

DevOps Engineer Certificate Program | Transcript 

OTP-AWSD5: AWS IoT: Developing and Deploying an Internet of Things

[PCEP-30-01] PCEP – Certified Entry-Level Python Programmer

Quotes I Like

"Failure will never overtake me if my determination to succeed is strong enough."  - Dr. APJ. Abdul Kalam

"Always be yourself, express yourself, have faith in yourself, do not go out and look for a successful personality and duplicate it." - Bruce Lee

"Technology is just a tool. In terms of getting the kids working together and motivating them, the teacher is the most important." - Bill Gates

"Innovation distinguishes between a leader and a follower." - Steve Jobs

CategoryList

Disclaimer

The information provided here is based on my expreriences, troubleshooting and online/offline findings. It can be used as is on your own risk without any warranties and I impose no rights.