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.
Issue
Methods are needed to purge access events with Security Expert running on SQL Express
Product Line
EcoStruxure Security Expert
Environment
- EcoStruxure Security Expert
- Microsoft SQL Express
- Microsoft SQL Server Management Studio (SMSS)
Cause
Security Expert operating on Microsoft SQL Express can exceed the 10GB limitation depending on configuration and system size
Resolution
There are several things to consider when configuring Security Expert when running on Microsoft SQL Express which has a 10GB size limitation and other limitations.
This article will address the following items: Backup of the SecurityExpert and SecurityExpertEvents databases, review of the database file sizes, automatically and/or manually purge access event records, shrink the database(s) to regain hard drive space, and return the system to operational status.
1. Create database backups for the SecurityExpert and SecurityExpertEvents databases
(Select a method)
a. Configure options in Security Expert under Global Settings> General> Main Database Backup
b. Use Microsoft SMSS. Right-click on the database, select Backup and complete the steps
Note: It is recommended to save the databases as different file names than the original database
names to keep a copy of the database prior to deleting records.
2. Examine the database file sizes
a. Observe the file size In Windows Explorer, Navigate to:
C:\Program Files\Microsoft SQL Server\MSSQL(version).SECURITYEXPERT\MSSQL\DATA
Observe the file size of the SecurityExpertEvents database
If the site is using Microsoft SQL Express and the database file size is close to or over 10GB,
events will need to be purged.
Note: SecurityExpertEvents files. One file is the transaction log and the other is the data file.
The file highlighted above is the data file.
b. Check the Windows Application log:
There may be a message with evidence of database size issues in the Windows application
log, i.e. "Primary Filegroup is FULL"
3. Check the database recovery model configured for both databases in SMSS
If "FULL" is used instead of SIMPLE the database(s) will grow quickly
See the following KB article for more details on Recovery Mode:
https://community.se.com/t5/Building-Automation-Knowledge/Security-Expert-functionality-starts-to-de...
An article with additional details about the database table sizes:
https://community.se.com/t5/Building-Automation-Knowledge/Security-Expert-Database-Table-Sizes/ta-p/...
4. Purge the SecurityExpertEvents db (Select a method)
a. Purge using Security Expert
Under Global Settings> General> Events Database
Configure Purge events older than – there is a drop-down with a list of selections
See the Security Expert About> Help for more information.
Search on Purge and select Global Settings
Note: It’s best practice to delete a month or so at a time. Deleting many events at a time can
cause the server to time out.
After the Security Expert purge, use SMSS to Shrink DB and Shrink File
(These actions remove the recently deleted records from the hard drive freeing up space
Right-click on the SecurityExpertEvents database> Shrink> database
Right-click on the SecurityExpertEvents database> Shrink> File
b. Manually delete access events using SQL queries
In SMSS, run the following query to retrieve all access events and make note of the number of
records
select * from events order by logged time asc
Example: Note 2840 rows
To purge the records run the following query:
(Caution: these records will be removed from the database)
Use SecurityExpertEvents;
Delete from dbo.Events
Where LoggedTime BETWEEN '2024-06-13' AND '2024-06-14'
Adjust the above query to reflect the dates required)
8 rows affected
select * from events order by logged time asc – after running the delete query
Example:2832 rows
Note: It’s best practice to delete a month or so at a time. Deleting lots of events at a time can
cause the server to time out.
c. Shrink database
This action removes the recently deleted records from the hard drive freeing up space
Right-click on the SecurityExpertEvents database> Shrink> database
5. Verify Security Expert functionality
Once the purge and shrink are completed, verify Security Expert is operating normally
Note: If the system is not working normally, restoring a previous version of the database(s) from earlier backups may be necessary that were taken before exceeding the 10GB limitation.
DBCC is a Microsoft tool for checking the integrity of a SQL database which is beyond the scope of SE Product Support. Please refer to Microsoft's documentation on this subject or consult a Database Analyst.
