Summary
• Applies to Microsoft SQL Server Express with a 10 GB database size limit.
• When the database reaches this 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.
• In failure scenarios, SQL Server may switch the database to SINGLE_USER or EMERGENCY mode.
Database Modes
| Mode | Description |
|---|---|
| SINGLE_USER | Transactions are rolled back. Database is available for administrative maintenance. |
| EMERGENCY | Indicates possible corruption. Database may be in inconsistent state. Data recovery may not be possible. |
This procedure reduces database size before or after installing a new TRBOnet version, prior to database upgrade.
Before New Version Install
1. Remove old or unnecessary data using TRBOnet database maintenance tools.
2. Create a backup.
3. Proceed with software and database upgrade.
After New Version Install Without Database Upgrade Attempt
If Backup Is Available
1. Roll back to the previous TRBOnet version.
2. Restore the database from backup.
3. Remove old or unnecessary data.
4. Create a new clean backup.
5. Proceed with software and database upgrade.
If Backup Is Not Available
Step 1: Check Database State
1. Open SQL Server Management Studio.
2. Verify the database is in mode.
Step 2: Stop TRBOnet Service
1. Stop the TRBOnet service.
2. Keep the service stopped during maintenance.
Step 3: Identify Large Tables
Check which tables consume the most space.
USE {database_name};
GO
sp_msforeachtable N'EXEC sp_spaceused [?]';
GO
GO
sp_msforeachtable N'EXEC sp_spaceused [?]';
GO
Notes
• The script shows table size including indexes and related objects.
Step 4: Rebuild Indexes
1. Rebuild indexes with fragmentation greater than 20 percent.
2. Start with the largest tables.
Step 5: Shrink the Database
Shrink the database to reclaim reserved space after index rebuild.
Step 6: Delete Old Data
Use stored procedure .
Example for table :
DECLARE @return_value int
EXEC @return_value = [dbo].[sp_ClearOldData]
@tableName = N'GpsInfo',
@dateColumnName = N'date',
@date = '2022-11-07 00:00'
EXEC @return_value = [dbo].[sp_ClearOldData]
@tableName = N'GpsInfo',
@dateColumnName = N'date',
@date = '2022-11-07 00:00'
The procedure deletes records older than the specified date in batches using multiple transactions.
Notes
• Replace the example date with the required value.
• TRBOnet uses .
• Time is stored in UTC with offset.
• Specify time zone offset when required.
• Example offset corresponds to UTC+3.
Standard DELETE Example
DELETE FROM [TRBOnet].[dbo].[GpsInfo]
WHERE [date] < '2022-11-07 00:00 +03:00'
WHERE [date] < '2022-11-07 00:00 +03:00'
Step 7: Shrink the Database
Shrink the database again after deleting data to reclaim freed space.
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 mode.
• No transition to or mode occurs.
• TRBOnet services operate normally after upgrade.
• New data is written without errors.