SQL Database growing over the size of 10Gb

Answered

Hi All,

We have a TeamCity server with a SQL Server Express database (limited to ~10Gb. It was installed and configured in 2017. The database was initially created with an initial size of 10Gb. We configured cleanup rules to avoid to keep useless history files and useless information.

But recently we faced the problem that the database could not automatically growth due the limit of the SQL Server Express database. To solve the problem we moved to a real SQL Server but we would like to know why the database grow indefinitely? We thought that having some cleanup policies would have prevented such a problem.

All your advises are welcome.

Thanks in advance,

Stephane Rancurel

7 comments
Comment actions Permalink

Hi!

It would be useful to know what tables are the top disk consumers. Perhaps, you could configure stricter clean-up policies to clean the builds more aggressively. But please note that currently some tables are not cleaned up, e.g., the vcs_change table. There's a related task in our tracker: TW-25099.

Cheers,
-Anatoly 

0
Comment actions Permalink

Hi Anatoly,

Thanks for your reply. I launched a request in the database to get the size used by each tables of the Database. The table test_names consume 10Gb of the space. Please have a look to the screenshot below.

Thanks in advance for your help

Stephane

0
Comment actions Permalink

Stephane,

The test_names table contains all test names seen by TeamCity server in your builds. TeamCity should clean it up according to the Clean-up Rules in the project settings. 6.8 million rows are quite a lot, meaning most likely, the builds running the tests are not cleaned up.

The SQL query below should shown you which build configurations have the most test names associated with them. It would make sense to look at the clean-up rules applied to those configurations. Please, ensure they are strict enough and work as expected.

SELECT
build_type_mapping.ext_id as build_configuration_id,
tests_per_bt.total_test_count as total_test_names
FROM
(
SELECT
history.build_type_id,
SUM(tests_per_build.test_count) as total_test_count
FROM
(
SELECT
test_info.build_id,
COUNT(test_names.id) as test_count
FROM
test_info
INNER JOIN test_names ON test_info.test_name_id = test_names.id
GROUP BY
test_info.build_id
) as tests_per_build
INNER JOIN history ON history.build_id = tests_per_build.build_id
GROUP BY
history.build_type_id
) as tests_per_bt
INNER JOIN build_type_mapping ON build_type_mapping.int_id = tests_per_bt.build_type_id
ORDER BY
total_test_names DESC

-Anatoly 

0
Comment actions Permalink

Hi Anatoly,

I ran the SQL request and got the id of the build configuration that contains most of the test_names.

Then I had a look to the clean-up rules of the project and it is set as the other projects.

Any advise on this will appreciated.

Stephane

0
Comment actions Permalink

Stephane,

Apparently, clean-up isn't removing the build history for some reason. Besides not having enough successful builds in a branch to satisfy the criteria specified in your clean-up rule, the builds won't be cleaned up if:

  1. The build is pinned.
  2. The build is a dependency for another build in the chain. You can tell that by the presence of the chain link icon on the build in the build history, or using these SQL selects:
SELECT * FROM build_dependency WHERE depends_on = <build_id>;
SELECT * FROM downloaded_artifacts WHERE source_build_id = <build_id>;

-Anatoly 

0
Comment actions Permalink

Anatoly,

I ran your two last queries, and there are no depency chain for the project, and no builds are pinned.

It seems that the Clean-up do some work. It ran this morning and I re-ran your SQL request and here is the result:

So it seems that records are being removed but the size of database is still growing. Please have a look to the size used by the table compared to my first message.

Thanks for your help

Stephane

0
Comment actions Permalink

Stephane,

I'd recommend taking a look at the build history and making sure your clean-up policies work as expected, i.e. there aren't any unexpectedly old builds in the build configuration history. If the policies do in fact work as expected, you might need to make them stricter to keep even fewer builds in history.

-Anatoly 

0

Please sign in to leave a comment.