Truncating MSSQL transaction logs

When GitHub Actions is enabled, MSSQL transaction logs grow until a backup truncates them. If backups are not running often enough, the logs can fill the data disk on your GitHub Enterprise Server instance.

In this article

Symptoms

MSSQL runs on your GitHub Enterprise Server instance only when GitHub Actions is enabled. When its transaction logs grow too large, you may see any of the following.

  • Data disk usage climbs steadily and will not come down.
  • MSSQL logs errors stating that the transaction log for a database is full.
  • Self-hosted runners fail to register. A single transaction log cannot grow beyond 2 TB, and once it reaches that limit, writes to that database fail.
  • MSSQL replication fails, or takes much longer than expected to complete.

Causes

Each MSSQL database has a data file ending in .mdf and a transaction log ending in _log.ldf. Every change is written to the transaction log first.

In a high availability configuration, the databases run in the FULL recovery model, which replication requires. In that model, log space is only made reusable when a backup runs. If backups are not running often enough, or are failing, the log keeps extending the file to hold new records. It will grow until it fills the data disk, or until it reaches the 2 TB per file limit, whichever comes first.

Confirming the problem

  1. SSH into the administrative shell. For more information, see Accessing the administrative shell (SSH).

  2. List the MSSQL data directory.

    sudo ls -lth /data/user/mssql/data

  3. Compare each .mdf data file against its matching _log.ldf transaction log, and identify which files are using the most space.

    A large _log.ldf file is not proof on its own that truncation is failing. Truncation makes space inside the file reusable, it does not shrink the file, so a log that has been truncated successfully can still be large. What the listing tells you is which files are consuming the disk.

    -rw-r----- 1 mssql mssql 156G Apr 17 02:52 Mps_cf856db1-88f0-412a-b35d-8df36a8350ac_log.ldf
-rw-r----- 1 mssql mssql  72M Apr 17 02:52 Mps_cf856db1-88f0-412a-b35d-8df36a8350ac.mdf
-rw-r----- 1 mssql mssql 836M Apr 17 02:52 Pipelines_45f96584-c933-460c-8706-5d8928261202_log.ldf
-rw-r----- 1 mssql mssql  72M Apr 17 02:52 Pipelines_45f96584-c933-460c-8706-5d8928261202.mdf
-rw-r----- 1 mssql mssql 3.4G Apr 17 02:58 Mps_Configuration_log.ldf
-rw-r----- 1 mssql mssql 136M Apr 17 02:58 Mps_Configuration.mdf

    Every log here is more than five times its data file, which is well past normal growth. In this example the first log file is 156 GB against a 72 MB data file, so it is the one filling the disk.

Resolving the problem

The procedures below temporarily switch the databases to the SIMPLE recovery model, which allows the transaction logs to be truncated, then restores the FULL recovery model.

Follow the procedure that matches your environment. The two differ because replication relies on the FULL recovery model, so in a high availability configuration replication has to be disabled before the logs can be truncated.

High availability environments

Use this procedure if your GitHub Enterprise Server instance has one or more replicas. Run every command on the primary.

Warning

This procedure drops the availability group and deletes the MSSQL database files on each replica, which are then recreated by seeding. Seeding a large database takes time, and your replicas are not protecting you until it finishes.

  1. Take a manual backup using your configured backup solution.

    ghe-backup

    If no backup solution is configured, export the MSSQL databases instead.

    ghe-export-mssql --compress --stats 1

  2. Record the current file sizes, so you can confirm the reduction afterwards.

    sudo du --all --human-readable /data/user/mssql/data

  3. Truncate the transaction logs. This disables MSSQL replication on every replica, drops the availability group, switches the databases to the SIMPLE recovery model, and shrinks the logs.

    sudo /usr/local/share/enterprise/ghe-mssql-shrinkfile --disable-mssql-replication --set-simple-recovery-model

  4. Confirm the files are smaller.

    sudo du --all --human-readable /data/user/mssql/data

  5. Check that the MSSQL containers are healthy.

    ghe-actions-check -s mssql

  6. Re-enable MSSQL replication. This recreates the availability group and restores the FULL recovery model.

    ghe-cluster-each --serial --exclude --role mssql -- /usr/local/share/enterprise/ghe-repl-start-mssql

  7. Monitor seeding until the replicas are in sync. Until seeding finishes, the replicas hold no MSSQL data.

    Unlike the steps above, run this on each replica rather than the primary.

    ghe-repl-status

Standalone environments

Use this procedure if your GitHub Enterprise Server instance has no replicas.

  1. Take a manual backup using your configured backup solution.

    ghe-backup

    If no backup solution is configured, export the MSSQL databases instead.

    ghe-export-mssql --compress --stats 1

  2. Record the current file sizes.

    sudo du --all --human-readable /data/user/mssql/data

  3. Truncate the transaction logs. This switches the databases to the SIMPLE recovery model and shrinks the logs.

    sudo /usr/local/share/enterprise/ghe-mssql-shrinkfile --set-simple-recovery-model

  4. Confirm the files are smaller.

    sudo du --all --human-readable /data/user/mssql/data

  5. Check that the MSSQL containers are healthy.

    ghe-actions-check -s mssql

  6. Take a fresh backup. This restores the FULL recovery model.

    ghe-export-mssql --compress --stats 1

    Warning

    On a standalone instance, the FULL recovery model is not restored automatically. You must take this backup. If the databases stay in the SIMPLE recovery model, transaction logs are truncated at every checkpoint, and replication will fail if you add a replica later.

Preventing recurrence

Transaction logs are cleared by backups, so the logs stay a reasonable size only while backups run reliably.

  • Run backups on a schedule, and check that they are completing. For more information, see Configuring the backup service.
  • Monitor data disk usage so that you see the logs growing before the disk fills. For more information, see Recommended alert thresholds.
  • After any period where backups were failing, check the file sizes again, because the logs will have grown in the meantime.

Contacting GitHub Support

If these steps do not resolve the problem, or you are unsure about any of them, contact GitHub Support rather than continuing. For more information, see Creating a support ticket.

If you have already opened a ticket, attaching a support bundle helps GitHub Support diagnose the problem faster. For more information, see Providing data to GitHub Support.

Further reading