SonarQube 6.7.1数据库异常膨胀问题排查求助

Let’s break down the root causes behind your bloated 20GB database (vs. the expected 1.2GB for 3.5M LOC) by focusing on the top three tables you identified, plus context about your SonarQube version and cleanup settings:

1. ce_scanner_context: The Biggest Offender #

This table stores scanner context data (like scan configurations, dependency lists, and environment details) from every analysis run. Here’s why it’s ballooning:

• SonarQube 6.7.1 Bug: Older versions (pre-7.9) of SonarQube do NOT automatically clean up this table with the Database Cleaner setting you configured. The "Delete all analyses after" rule only targets analysis reports and related metrics, not scanner context records.
• Frequent Scans: If you’re running scans often (e.g., per PR, daily builds), each scan adds a new entry to this table. Over 360 days, that’s thousands of entries piling up with no automatic cleanup.

2. project_measures: Accumulated Metric Data #

This table holds all your code metrics (coverage, complexity, duplication, etc.). While 6GB is large for 3.5M LOC, here’s what’s driving it:

• Uncleaned Branches/PRs: If you’re scanning feature branches or pull requests and not deleting old, unused branches, each branch’s metrics are stored separately. This multiplies your data volume significantly.
• 360-Day Retention: Even for your main branch, keeping 360 days of historical metrics adds up—especially if you run multiple scans per day.
• Abandoned Projects: If you have old projects in SonarQube that you no longer use, their metric data is still taking up space in this table.

3. issues: Unmanaged Problem Records #

At 2GB, this table is smaller than the other two, but it’s still contributing to the bloat:

• Unresolved/Uncategorized Issues: If you have millions of issues that are either still open, or marked as "Open" but actually false positives, they’ll stay in the table indefinitely.
• 360-Day Retention: Unlike metrics, issues aren’t always tied to specific analyses—so even old issues from 360+ days ago might not be getting cleaned up depending on your configuration.

Fixes to Shrink Your Database #

Immediate Actions #

• Manually Clean ce_scanner_context: Run this SQL query to delete records older than 360 days (match your cleanup window):

  DELETE FROM dbo.ce_scanner_context 
  WHERE created_at < DATEADD(day, -360, GETDATE());
  

  After deleting, rebuild the table’s indexes to reclaim wasted space (fragmented indexes add unnecessary bulk).
• Delete Abandoned Projects/Branches: Go to the SonarQube UI, find projects you no longer need, and delete them. For branches, use the branch management tools to prune old, unused ones—this will automatically remove their associated metrics and issues.
• Triage Old Issues: Bulk-mark false positives as "False Positive" or "Won’t Fix", and close resolved issues. This makes them eligible for cleanup in future runs.

Long-Term Solutions #

• Upgrade SonarQube: Version 6.7.1 is end-of-life (released in 2018) and lacks modern cleanup features. Newer versions (7.9+) automatically clean up ce_scanner_context and have better branch/PR cleanup logic. Upgrading will also fix security vulnerabilities and improve performance.
• Adjust Cleanup Settings: If you stick with 6.7.1, consider reducing your retention window from 360 days to something more reasonable (e.g., 180 days) to limit metric accumulation. For PR scans, enable settings to delete PR analysis data after the PR is merged.
• Optimize Scan Frequency: Avoid running unnecessary scans (e.g., multiple scans per day for the same branch unless code changes frequently).

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