Issue
A method is required to purge orphaned ImageData records from the Security Expert database to free up hard drive space.
Product Line
EcoStruxure Security Expert
Environment
- Security Expert
- Microsoft SQL Server Management Studio (SSMS)
Warning
Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support for assistance.
Cause
Running the SSMS Disk Usage report indicates what tables are the largest. The ImageData table is often one of the largest in the report and queries are needed to purge records.
During transitions from Continuum, if Personnel/User records are removed, the GA_Blob table converts to the ImageData table.
However, orphaned records may remain in ImageData because deletions in Continuum or Security Expert do not automatically remove associated image records.
Resolution
Use the instructions below to identify and select all ImageData records that do not belong to a user, then delete them from the database.
Note: Always validate the results of the SELECT query before running the DELETE command.
✅ Step 1: Backup the Database
In SSMS:
- Right-click the
SecurityExpertdatabase - Navigate to:
Tasks > Backup - Complete a successful backup before proceeding.
✅ Step 2: Identify Orphaned Records
Run the following SELECT query in SSMS to identify orphaned ImageData records:
USE SecurityExpert
SELECT dbo.ImageData.ImageID
FROM dbo.ImageData
WHERE NOT EXISTS (
SELECT ImageID
FROM dbo.Users AS T1
WHERE T1.ImageID = ImageData.ImageID
)
This query will return all ImageData entries not linked to any user.
✅ Step 3: Purge Orphaned Records
Run the following DELETE query to remove the orphaned records:
USE SecurityExpert
DELETE FROM dbo.ImageData
WHERE NOT EXISTS (
SELECT ImageID
FROM dbo.Users AS T1
WHERE T1.ImageID = ImageData.ImageID
)
✅ Step 4: Shrink the Database
After deletion:
- Right-click the
SecurityExpertdatabase - Navigate to:
Tasks > Shrink > Database
⏳ Note: Depending on the database size and number of records, this process may take several minutes.
