Optimizing SOTI XSight Cook Database Size to Address SQL Express 10GB Limit

The SOTI XSight Cook database may exceed the 10GB limit imposed by SQL Express. This issue can arise from failed scheduled tasks or missing scripts, necessitating manual intervention to reduce the database size and restore normal functionality.

The SQL script provided in this knowledge base article is specifically applicable to SOTI XSight version 4.2.1.81. For other versions of SOTI XSight, please navigate to C:\Program Files\SOTI\XSight\Insight6\Jobs on the SOTI XSight server to obtain the appropriate script.

• SOTI XSight Cook database reaching or exceeding the 10GB limit.
• Oversized log files consuming excessive space on the drive.

• Ensure that the scheduled SQL script for database maintenance is correctly configured in Task Scheduler and executes at the designated times.
• Regularly verify that the script file is located in the correct path and is accessible by the system.

The SOTI XSight Cook database can exceed the 10GB limit imposed by SQL Express, potentially impacting performance or functionality. This typically occurs due to the following reasons:

• A scheduled script in Task Scheduler failed to execute at its designated time.
• The script file has been removed or is missing from the path specified in Task Scheduler.

Important Prerequisites: 

Before proceeding, ensure you have completed the following:

• Take a full server snapshot to capture the current state of the system.
• Perform a full database backup to avoid data loss in case of any issues during the process.

The Cook DB Cleanup script for SOTI XSight is located at: C:\Program Files\SOTI\XSight\Insight6\Jobs

To prevent data conflicts and maintain database integrity, it's recommended to stop all SOTI XSight services before executing the script manually. Follow these steps:

1. In the Services panel, stop all SOTI XSight-related services on the server.

2. Connect to your SQL Server and select the Cook database.

3. Run the SQL script manually if the automatic task has not executed.

4. Start SOTI XSight Services.

5. Ensure the SOTI XSight console is fully operational and the database size is within acceptable limits.

Shrink the Database:

This script switches the recovery model, shrinks the log and data files, and then sets the recovery model back to full. This step should be run first to reduce database size.

//DB Cleanup script for Xsight 4.2.1.81

-- Declare variables for database name, log file, and data file
DECLARE @dbname    NVARCHAR (128) = DB_NAME();
DECLARE @file_log  NVARCHAR (128) = (SELECT name FROM sys.database_files WHERE type = 1);
DECLARE @file_rows NVARCHAR (128) = (SELECT name FROM sys.database_files WHERE type = 0);

-- Set recovery model to SIMPLE to allow shrinking of the log file
EXEC ('ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE');

-- Shrink the log file and data file
EXEC ('DBCC SHRINKFILE (' + @file_log + ', 1)');
EXEC ('DBCC SHRINKFILE (' + @file_rows + ')');

-- Set recovery model back to FULL
EXEC ('ALTER DATABASE ' + @dbname + ' SET RECOVERY FULL');

Post-Shrink Actions:

After executing the script, verify that the database size has reduced as expected. Additionally:

• Check Task Scheduler settings to confirm the script is correctly set up for future scheduled executions.
• Ensure the script file is accessible in the designated path for the Task Scheduler.