Database Properties (Function TRTPROP)
Ever come across a function that you haven't used or even know about? —let’s look at Database Properties (Function TRTPROP).
You can access this function by navigating to:
Development → Utilities → Verifications → Database → Properties
Before we dive into some of the valuable insights this tool provides, take a moment to look at what online help says about it, you’ll find the link and description below.
https://online-help.sagex3.com/erp/12/en-us/Content/FCT/TRTPROP.htm?Highlight=TRTPROP
As noted, this tool will display the current database properties.
We’re selecting SQL Server properties for the purpose of this blog. (Link below)
https://online-help.sagex3.com/erp/12/en-us/Content/FCT/SQLPROP.htm
After selecting this function, which illustrates properties of your database, there is some information available to you that you may find useful.
The server section of this function provides a quick technical snapshot of the SQL Server environment behind the selected Sage X3 database. At the top, it identifies the database name and the SQL Server version it is running on—here, Microsoft SQL Server 2022, build 16.0.4225.2 (X64).
The Server section breaks down additional details about the hosting environment:
- Machine Name: The Windows server hosting SQL Server.
- SQL Instance: The specific SQL Server instance used by Sage X3.
- Default Collation: The database collation (Latin1_General_BIN2), which defines sorting and comparison rules—important for Sage X3 performance and behavior.
- Full SQL Version Info: This includes the SQL engine edition (Developer Edition), OS version (Windows Server 2025 Standard), and build metadata such as CU level and build number.
In short, this page gives administrators a consolidated view of the SQL Server platform supporting Sage X3, which is useful for troubleshooting, audits, upgrades, and ensuring the environment matches Sage’s technical requirements.
The Database section of the Properties function gives a detailed snapshot of the Sage X3 database itself—its size, configuration, activity level, and backup status.
Key details include:
- Database Owner: Identifies the SQL login that owns the database.
- Creation Date & Time: Shows when the database was originally created, useful for tracking environment history.
- User Activity: Displays the number of users defined for the database and the number of currently active connections, giving insight into how busy the system is at that moment.
- Database Size: Shows the total size of the database along with unused space, helpful for monitoring growth and planning storage.
- Collation: Confirms the classification (Latin1_General_BIN2), which is essential for Sage X3 compatibility and case‑sensitive behavior.
- Recovery Model: Indicates the SQL Server recovery model—here, SIMPLE, which affects how backups and transaction logs are managed.
- Compatibility Level: Displays the SQL Server compatibility mode (SQL Server 2022 – level 160), ensuring the database operates with the correct SQL feature set for the server version.
- Backup Status: Provides placeholders for the last full, differential, and transaction log backups, enabling quick confirmation of backup health (As this in an internal VM we don’t schedule DB backups.
From the Options section you can see a list of values currently configured, mostly set as defaults but can be modified for performance and maintenance tuning.
- Database Identity & Setup
Name, creation date, compatibility level, and collation help confirm the database was created correctly and is aligned with Sage X3 requirements.
The collation (Latin1_General_BIN2) and compatibility level (SQL Server 2022) are especially important because they directly impact how X3 behaves.
- Access & Operational State
User access mode, read‑only status, and current state show whether the database is online, writable, and available to multiple users.
These values matter when troubleshooting login issues or blocked environments.
- Performance‑related Settings
Options like auto‑create/update statistics, auto-shrink, and auto-close influence performance and should follow best‑practice configurations.
X3 performs best when auto‑stats are enabled and shrink/close features are turned off.
- Recovery & Safety
Recovery model and page‑verify settings determine how backups and corruption detection work.
SIMPLE recovery is common for test systems, while production databases often use FULL.
CHECKSUM page verification provides stronger data protection.
- Isolation & Logging
Options related to snapshot isolation and supplemental logging show how SQL handles row locking and change tracking.
These matter more in complex environments with high concurrency or data replication.
In short:
The Options screen is essentially the “technical DNA” of the Sage X3 database. While it lists dozens of values, only a core set directly influences stability, performance, and compliance with X3’s required configuration. For system admins, this view serves as a quick health‑check to ensure the database is running with the right settings and nothing has drifted from best practices. See more from Online Help for this section:
Search Index (SQLDICO)
The second function I want to highlight today is Search Index (SQLDICO).
You can access it by navigating to:
Development → Utilities → Verifications → Database → Search Index
Selecting this function will automatically run the search index, you will be prompted to confirm the search comparison to the X3 folder, from whatever folder you are currently connected to.
From online help, you’ll find the following description.
https://online-help.sagex3.com/erp/12/en-us/Content/FCT/SQLDICO.htm
When you run this tool, you will get a log file similar to below.
The Search Index (SQLDICO) function scans the database and compares the actual SQL Server indexes with those defined in the Sage X3 dictionary. The output provides a detailed count of expected indexes, missing indexes, inactive ones, and any extra indexes found directly in SQL Server but not defined in X3.
In the example shown, the report lists:
- The total number of indexes described in the X3 dictionary
- How many optimization indexes should exist
- Indexes missing due to inactive activity codes or inactive tables
- Dictionary indexes that do not exist in SQL Server
- Internal or system-generated indexes
- Indexes present in SQL Server but not described in X3
- Indexes related to large objects (image/text fields)
This results in a final comparison between the theoretical index structure defined by Sage and the actual index structure on the SQL Server instance.
The Search Index report acts as a health check for your Sage X3 database indexing. It highlights mismatches, missing components, and optimization opportunities—giving administrators a clear path to improving database performance and ensuring consistency with Sage’s data dictionary.
I hope this helps beef up your Sage X3 knowledge so you can use the application to its fullest.
