This article describes how to reduce TRBOnet database size to prevent upgrade failures and database corruption when using Microsoft SQL Server Express with a 10 GB database size limit.

When the database reaches the limit, new data cannot be written. During a TRBOnet upgrade, the database structure changes and size may increase by 20 to 30 percent.

If the database size is close to the limit at 8 to 10 GB, the upgrade may fail or cause database corruption.

Database Modes

Before new version install

Database upgrade attempt was unsuccessful due to database size limit

Step 1: Check Database State

Step 2: Stop TRBOnet Service

1. Stop the TRBOnet service before making database changes.

2. Keep the service stopped during maintenance to prevent new data from being written to the database.

Step 3: Identify Large Tables

Before making changes, check which tables use the most space.

USE {database_name};
GO
sp_msforeachtable N'EXEC sp_spaceused [?]';
GO

This script shows the size of each table, including related objects such as indexes.

Step 4: Rebuild Indexes

1. Rebuild indexes where fragmentation is greater than 20%.

2. Focus first on the largest tables identified in Step 3.

Step 5: Shrink the Database

After rebuilding indexes, SQL Server still reserves space based on previous data size.

To reclaim unused space, shrink the database:

Step 6: Delete Old Data

Use procedure "sp_ClearOldData" with TRBOnet versions 5.7 and newer

Example for the table with GPS data [GpsInfo]:

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_ClearOldData]
@tableName = N'GpsInfo',
@dateColumnName = N'date',
@date = '2022-11-07 00:00 +03:00'

The procedure deletes all records older than the specified date, starting from the oldest records. Make sure the date is correct before running the script.

Important Notes

• Replace the example date with your own date.

• TRBOnet uses datetimeoffset in the database.

• Time is stored in UTC together with a time zone offset.

• A time zone offset must be specified in the scripts when applicable.

• In the example above, +03:00 represents a UTC+3 time zone.

• To find out the datetimeoffset use SSMS → TRBOnet Database → 'Gps.Info' table → Select Top 1000 Rows → 'date' column.

Use DELETE command for TRBOnet versions before 5.7

Example for the table with GPS data [GpsInfo]:

DELETE FROM [TRBOnet].[dbo].[GpsInfo]
WHERE [date] < '2022-11-07 00:00'

Important Notes

• Replace the example date with your own date.

Step 7: Shrink the Database

After deleting old data, perform another database shrink to reclaim the freed space.

1. Open the database context menu.

2. Select Tasks → Shrink → Database.

Step 8: Run Database Upgrade

Run the database upgrade after all maintenance steps are completed.

Expected Result

• Database size is reduced below SQL Server Express limit.

• Free space is available for database structure changes during upgrade.

• Database remains in MULTI_USER mode.

• No transition to SINGLE_USER or EMERGENCY mode occurs.

• TRBOnet services operate normally after upgrade.

• New data is written without errors.