Troubleshooting SoftGrid Database Growth Issues

  • Article

A SQL Database issue that we have seen in the field with SoftGrid 4.1 Servers is the SoftGrid SQL database growing very large and reaching a point where the SoftGrid server becomes sluggish and starts showing signs of intermittent connectivity problems with the SQL Server. You will see 1105 SQL errors in the system application log and numerous entries like the following in the sft-server.log:

[2007-07-05 15:00:09.875] SFTHOST1 3424 3464 SW_SQLOutputHandler::HandleMessage -
- - - 1 41538 "Failed to log message to SFTHOST1. Got unknown error while creating record in data store."

On a clean install of SQL 2000 or SQL 2005 and SoftGrid 4.1 by default the SQL Server Agent service is not enabled and the 3 or 4 SoftGrid SQL Jobs (3 for 3.2 and 4 for 4.1) never run which causes unrestricted growth of the SoftGrid Database. Please be sure to enable this service by right clicking on SQL Server Agent and selecting start from the context menu.

To correct this first try manually running the “Check Usage History” and “Enforce Size Limit” jobs by right clicking on them and choosing Start Job.  SQL 2005 will immediately bring up a status window and let you know if it succeeded or failed while SQL 2000 logs an event in the system application log.

Make sure to check the SoftGrid Server System Options settings and ensure that the “Database Size: Maximum Database Size (MB)” and Usage History: Keep Usage for (Months) are selected and set to the defaults; 1024 & 6 respectively. You may need to change the “Keep Usage for (Months)” setting down to 1 or 2 months and manually run the “Check Usage History” job to get the database trimmed down to an optimal size. If you modify the Database Size please ensure that you have enough disk space to accommodate the change.

If all else fails backup the SoftGrid database and truncate the MESSAGE_LOG and APPLICATION_USAGE tables on the SQL server. Issue the following SQL commands from the Query Analyzer:

truncate table MESSAGE_LOG
go

truncate table APPLICATION_USAGE
go

More Information: The SoftGrid installation creates 4 Jobs in the SQL jobs section:

  • Check Usage History
  • Close Orphaned Sessions
  • Enforce Size Limit
  • Monitor Alert/Job Status

Check Usage History:

Description: This job calls a stored procedure in the associated SoftGrid database to cleanup usage data on a monthly basis.

Schedule: This job is scheduled to run on the first day of every month at 2:00am.

Executes: It will execute store procedure sp_SFTcheckusagehistory.Store

Procedure Description: sp_SFTcheckusagehistory

Determines the number of months defined in the database for keeping Usage history and then calculates the appropriate cutoff date to pass to the database cleanup procedure.

Close Orphaned Sessions:

Description: This job calls a stored procedure in the associated SoftGrid database to close orphaned sessions on a weekly basis.

Schedule: This job is scheduled to run every Sunday at 12:00:00 am.

Executes: It will execute store procedure sp_SFTclosesessions.Store

Procedure Description: sp_SFTclosesessions

Closes orphaned sessions, license sessions, application usage and any unreleased license assignments that were started (or assigned) prior to the cutoff date/time using the close date/time for the specified SoftGrid server.

Enforce Size Limit:

Description: This job calls a stored procedure in the associated SoftGrid database to cleanup usage data. An alert triggered by an increase in the database data file(s) size calls this job.

Schedule: No schedule assigned.

Executes: It will execute store procedure sp_SFTenforcesizelimit.Store

Procedure Description: sp_SFTenforcesizelimit

Confirms that the maximum database size defined has been exceeded and then calculates the appropriate cutoff date to pass to the database cleanup procedure. The cutoff date is calculated by determining the approximate amount of data that needs to be cleaned up from the MESSAGE_LOG and APPLICATION_USAGE tables.

Monitor Alert/Job Status:

Description: This job refreshes the alerts and other jobs associated with the SoftGrid database based on configuration data stored in the database. The job is run on a daily basis.

Schedule: This job is scheduled to run Daily at 12:00:00 am.

Executes: It will first execute store procedures sp_SFTrefreshjobs and then sp_SFTrefreshalerts.

Store Procedure Descriptions:

sp_SFTrefreshjobs:

Refreshes specific properties on each database job defined for this database based on settings stored in the database. If a database job does not exist, it is created.

It is intended that this procedure will only ever be run by:

  • Database installation or upgrade
  • Manual execution
  • The monitoring database job called "Monitor Alert/Job Status" Only a user with sysadmin or db_owner privileges can successfully execute this procedure.

sp_SFTrefreshalerts:

Refreshes specific properties on each database alert defined for this database based on settings stored in the database. If a database alert does not exist, it is created. It is intended that this procedure will only ever be run by:

  • Database installation or upgrade
  • Manual execution
  • The monitoring database job called "Monitor Alert/Job Status" Only a user with sysadmin privileges can successfully execute this procedure.

One last note: Please be aware that running any of these SoftGrid SQL Jobs during production hours may prevent users from accessing their SoftGrid Packages until the job completes. These utilities should be run during non-production hours.

Enjoy,

John Behneman | SoftGrid Support Engineer