TeamCity大型Microsoft SQL Server数据库优化咨询：31GB以上数据库缩减与增长抑制方案及排查方法

Hey there, let's tackle your TeamCity database bloat issue step by step— I’ve dealt with similar scenarios for TeamCity instances running on SQL Server, so here’s a practical breakdown of what you can do:

First, let’s get that 31GB database trimmed down:

• Trigger a manual TeamCity clean-up run
  Even if you have clean-up rules configured, sometimes scheduled runs might not catch up with backlogged data. Head to Administration > Clean-Up in your TeamCity UI, then click Run Clean-Up Now. Note: This could take a while on a large database, so schedule it during off-peak hours to avoid impacting users.

• Shrink SQL Server transaction logs
  If your database uses the Full recovery model, transaction logs often balloon to huge sizes if not backed up regularly. Here’s how to fix that:

  1. First, back up the transaction log (required before shrinking if you care about point-in-time recovery):

     BACKUP LOG [YourTeamCityDBName] TO DISK = 'C:\Path\To\Backup\TeamCity_Log_Backup.bak'
     

  2. Switch to Simple recovery mode temporarily (skip this if you need Full recovery for compliance):

     ALTER DATABASE [YourTeamCityDBName] SET RECOVERY SIMPLE
     

  3. Shrink the log file (adjust 100 to your desired minimum size in MB):

     DBCC SHRINKFILE ([YourTeamCityDBLogFileName], 100)
     

  4. If needed, switch back to Full recovery mode:

     ALTER DATABASE [YourTeamCityDBName] SET RECOVERY FULL
     

• Shrink database data files and rebuild indexes
  After TeamCity cleans up old data, the data files will have unused space. Shrink them, but note this can cause index fragmentation—so rebuild indexes afterward:

  1. Shrink the database (keep 10% free space as a buffer; adjust as needed):

     DBCC SHRINKDATABASE ([YourTeamCityDBName], 10)
     

  2. Rebuild indexes on large TeamCity tables (like builds, build_logs, changes, or artifact_content) to fix fragmentation:

     USE [YourTeamCityDBName];
     GO
     ALTER INDEX ALL ON builds REBUILD;
     ALTER INDEX ALL ON build_logs REBUILD;
     -- Repeat for other large tables
     GO
     

• Manually delete obsolete build data
  If your clean-up rules don’t cover old, unused build configurations or archived builds, go to your Build Configurations, select outdated ones you no longer need, and delete them in bulk. This can free up significant space quickly.

Now let’s make sure the database doesn’t balloon again:

• Validate your existing clean-up rules

  • Double-check rule scope: In Administration > Clean-Up, confirm each rule applies to all relevant projects/build configurations (not just a subset).
  • Review clean-up logs: Check Administration > Server Logs for entries tagged cleanup—look for errors, skipped builds (marked as Keep forever), or rules that aren’t executing as expected.
  • Tune retention policies: If you’re keeping years of build logs or thousands of historical builds, tighten the rules. For example, set build logs to retain for 3 months, and limit historical builds to 100 per configuration (adjust based on your needs).

• Identify the biggest space-hogging tables
  Run this SQL query to find which tables are taking up the most space—this will help you target your clean-up efforts:

  SELECT 
      t.NAME AS TableName,
      s.Name AS SchemaName,
      p.rows AS RowCounts,
      SUM(a.total_pages) * 8 AS TotalSpaceKB, 
      SUM(a.used_pages) * 8 AS UsedSpaceKB, 
      (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
  FROM 
      sys.tables t
  INNER JOIN      
      sys.indexes i ON t.OBJECT_ID = i.object_id
  INNER JOIN 
      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  INNER JOIN 
      sys.allocation_units a ON p.partition_id = a.container_id
  LEFT OUTER JOIN 
      sys.schemas s ON t.schema_id = s.schema_id
  GROUP BY 
      t.Name, s.Name, p.Rows
  ORDER BY 
      TotalSpaceKB DESC
  

  Focus on optimizing rules for the top 3-5 tables you see here.

• Check artifact storage configuration
  If TeamCity is storing artifacts in the database (instead of the default file system), that’s a common culprit for bloat. Go to Administration > Global Settings > Artifacts Storage and confirm you’re using the file system. If you were using DB storage, migrate artifacts to the file system, then clean up the old artifact data from the database.

• Control build log size
  Some builds generate massive logs (e.g., verbose debug output). For these configurations:

  • Set log size limits in the build configuration settings to auto-truncate large logs.
  • Adjust clean-up rules to delete old build logs more aggressively than other data.

• Set up regular monitoring

  • Use SQL Server Management Studio’s built-in database reports to track growth trends over time.
  • Configure alerts to notify you if the database grows beyond a certain threshold (e.g., 25GB) so you can intervene early.
  • Schedule daily clean-up runs during off-peak hours to prevent data from piling up.

• Clean up old change records and snapshots
  TeamCity retains change history and build snapshots which can accumulate over time. Update your clean-up rules to include retaining change records for a reasonable period (e.g., 6 months) and pruning old snapshots that aren’t needed.

Hope these steps help you get your TeamCity database back to a manageable size and keep it that way! If you hit any snags with specific steps, feel free to follow up with details.

内容的提问来源于stack exchange，提问作者Eric Hemmerlin