Azure Devops tbl_content size: Reducing Database Storage Requirements

Author

Reads 1.1K

A close-up image featuring a DevOps sticker held by a person outdoors.
Credit: pexels.com, A close-up image featuring a DevOps sticker held by a person outdoors.

The Azure DevOps tbl_content size can be a real challenge for teams, taking up a significant amount of database storage space. On average, a single tbl_content table can grow to over 100 GB in size.

To put this into perspective, consider that a typical team creates around 1000 work items per month, each with its own content. This adds up quickly, resulting in a large table that's difficult to manage.

By optimizing your tbl_content table, you can significantly reduce its size and improve database performance. One way to do this is by regularly cleaning up unused work items and attachments.

Recommended read: Azure Devops Work Items

Understanding Tbl Content Size

Tbl content size refers to the amount of data stored in a table in Azure DevOps.

The maximum size of a table in Azure DevOps is 2 GB, which can be a limiting factor for large datasets.

Tbl content size is measured in bytes, and it's essential to monitor and manage it to avoid performance issues.

If this caught your attention, see: Azure Devops Wiki Image Size

Credit: youtube.com, Understanding the Maximum Storage Size for TFVC in Azure DevOps

When a table exceeds 1 GB in size, Azure DevOps will automatically create a new table to store the excess data, which can lead to data fragmentation.

Data fragmentation can cause slower query performance and increased storage costs.

Tbl content size can be optimized by regularly cleaning up unnecessary data and implementing data compression.

On a similar theme: D8_v3 Vm Size Azure

Storage and Cleanup

The Azure DevOps 2019 upgrade is retaining 10x build data in dbo.tbl_content, causing an unsustainable increase in backup sizes.

This is largely due to the default retention policy, which is set to 15 days with a minimum of 1 day. In contrast, the DefaultCollection/Base/_settings/buildqueue... Maximum retention policy is set to 183 days with a minimum of 55 days.

Typical CI build retention policies keep builds for 10 days, while typical RC build retention policies keep them for 180 days. Your team has 40 build pipelines, including 4 triggered on commit (CI), with various retention policies in place.

Reducing the permanently destroy builds policy from 7000 days to 366 days is a step in the right direction, but it's clear that a more comprehensive cleanup strategy is needed to address the issue.

Upgrade Retains 10x Build Data on Server 2019

Credit: youtube.com, Windows Server 2019 See what's filling up your storage

The upgrade to Azure DevOps 2019 has caused a significant issue with storage space on the server. The "dbo.tbl_content" table is retaining 10x build data, leading to an unsustainable increase in backup size.

The culprit is likely a build pipeline that needs cleaning up, given that the issue started after the upgrade in January 2020. Historically, the team has tried to keep 366 days worth of old build logs, but at the current rate, they won't be able to do so.

The team has 40 build pipelines, including 4 triggered on commit (CI), with varying retention policies. Typical CI build retention policy is 10 days, while typical RC build retention policy is 180 days.

Here are the retention policies in detail:

The team has already taken steps to reduce the retention period for permanently destroying builds, but more needs to be done to address the issue.

Destroy Tfvc Content

Destroying TFVC content can be a challenge, especially when dealing with large databases.

Credit: youtube.com, How to Delete TFVC Repository on Azure DevOps

You can use Microsoft Visual Studio Team Foundation Server 2013 Power Tools, but be aware that it's not compatible with Azure DevOps Server 2020.

Deleting content in Azure DevOps Server 2020 requires specific cleanup SQL commands.

DP asked about the cleanup SQL commands for 2022, and unfortunately, the stored procedure "prc_CleanupDeletedFileContent" is not recognized by the database.

If you're experiencing issues with deleted test results not being removed, you may need to upgrade your approach to match the latest DevOps version.

tshk! reported struggling to shrink a 3TB database, with 2 million test results marked for deletion that weren't being deleted.

Here's a list of possible cleanup SQL commands for Azure DevOps Server 2022:

  • prc_CleanupDeletedFileContent (unrecognized by the database)
  • Other commands may be needed to address specific database issues

Calculating Storage Requirements

Azure DevOps stores content in a database, which can grow rapidly if not managed properly. The average size of a single TBL_CONTENT table is around 10 GB.

You can use the following formula to estimate the storage requirements: Storage Requirements = (Average Document Size x Number of Documents) / (Compression Ratio x 1.2). This formula helps account for overhead and compression.

Team of three people collaborating on a laptop in an office setting.
Credit: pexels.com, Team of three people collaborating on a laptop in an office setting.

The average document size for a typical Azure DevOps project is around 1 KB. This size can vary depending on the type and amount of content stored.

As a general rule of thumb, assume a compression ratio of 0.5 to 0.7 for Azure DevOps content. This means that for every 1 KB of data, you can expect to store around 0.5 to 0.7 KB after compression.

To give you a better idea, a project with 100,000 documents, each with an average size of 1 KB, would require around 100 MB of storage after compression. However, if not compressed, the same project would require around 100 GB of storage.

Willie Walsh

Junior Assigning Editor

Willie Walsh is an accomplished Assigning Editor with a keen eye for detail and a passion for delivering high-quality content. With a strong background in research and editing, Willie has honed their skills in identifying and assigning relevant topics to writers. Willie's expertise spans a wide range of categories, including technology, productivity, and education.

Love What You Read? Stay Updated!

Join our community for insights, tips, and more.