IN THIS ARTICLE

Save as PDF

Reducing TRBOnet Database size

This article explains how to reduce database size before or after new version install.

Before new version install

1. Remove old or unnecessary data using TRBOnet Database maintenance tools.

2. Take a backup.

3. Proceed with the software and database upgrade.

After new version install without Database upgrade attempt

If a backup is available

1. Roll back to the previous version of the software.

2. Restore the database from the backup.

3. Remove old or unnecessary data.

4. Create a final clean backup.

5. Proceed with the software and database update again.

If a backup is not available

Step 1: Check Database State

1. Open SQL Server Management Studio.

2. Verify that the database is in mode.

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.

SQL query result showing table sizes in SSMS

Step 4: Rebuild Indexes

1. Rebuild indexes where fragmentation is greater than .

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

The recommended method is to use the internal stored procedure sp_ClearOldData instead of a standard DELETE statement.

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'

This procedure processes data in batches using multiple transactions. It reduces transaction log growth and usually performs better than a standard DELETE.

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.

Standard DELETE example

 DELETE FROM [TRBOnet].[dbo].[GpsInfo]

 WHERE [date] < '2022-11-07 00:00 +03:00'

Important Notes

• Replace the example date with your own date.

• TRBOnet uses 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, represents a UTC+3 time zone.

Step 7: Shrink the Database Again

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

1. Open the database context menu again.

2. Select .

Step 8: Run the Update

Once all maintenance steps are completed, proceed with the update on the TRBOnet server.

Reducing TRBOnet Database size

This article explains how to reduce database size before or after new version install.

Before new version install

1. Remove old or unnecessary data using TRBOnet Database maintenance tools.

2. Take a backup.

3. Proceed with the software and database upgrade.

After new version install without Database upgrade attempt

If a backup is available

1. Roll back to the previous version of the software.

2. Restore the database from the backup.

3. Remove old or unnecessary data.

4. Create a final clean backup.

5. Proceed with the software and database update again.

If a backup is not available

Step 1: Check Database State

1. Open SQL Server Management Studio.

2. Verify that the database is in mode.

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 .

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

The recommended method is to use the internal stored procedure sp_ClearOldData instead of a standard DELETE statement.

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'

This procedure processes data in batches using multiple transactions. It reduces transaction log growth and usually performs better than a standard DELETE.

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.

Standard DELETE example

 DELETE FROM [TRBOnet].[dbo].[GpsInfo]

 WHERE [date] < '2022-11-07 00:00 +03:00'

Important Notes

• Replace the example date with your own date.

• TRBOnet uses 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, represents a UTC+3 time zone.

Step 7: Shrink the Database Again

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

1. Open the database context menu again.

2. Select .

Step 8: Run the Update

Once all maintenance steps are completed, proceed with the update on the TRBOnet server.

Helpful? Yes No

We're glad this article helped.

Thanks for letting us know. What went wrong?

The steps in the article did not resolve my problem

This article did not address what I was searching for

The wording used in this article made it confusing

Other

Please don't include any personal information in your comment.