Database Size and Performance

Most of the space in the adTempus database is used by job history: job instances, log messages, and related records. Retaining a large amount of history (for example, many jobs that each keep thousands of instances) can make the database very large, which can cause performance problems and, with SQL Server Express, can cause the database to reach its maximum size.

Symptoms

Slow response in the Console when selecting a job.
Frequent database error Alerts, or warnings in the diagnostic logs about database deadlocks or timeouts (for example, "SQL Server error 1205 ... was deadlocked on lock resources with another process"). adTempus retries failed database operations, so these often succeed on a later attempt; in that case no Alert is logged, but a warning is still written to the diagnostic log.
An Alert (ADT005385) and Event Log error reporting that the database is nearing the maximum size allowed for this edition of SQL Server.

SQL Server Express Size Limit

SQL Server Express limits the maximum size of a database (see Microsoft's documentation for the limit for your version). adTempus issues the ADT005385 Alert when the database reaches 75% of the limit.

Once the database reaches the size limit, adTempus can no longer write to it, and SQL Server may fail to load the database at startup. Address this warning immediately.

Reduce the amount of history being retained, as described below. If you need to keep large amounts of history, move the database to a full SQL Server instance (licensed separately from Microsoft).

Reducing Retained History

First determine whether you are keeping more history than you need, using the queries below. Then:

Review the default data retention settings on the General page of the Server Options window, and reduce them if appropriate.
Review the history retention settings for individual jobs. For example, even if you need to keep history for a year in general, a job that runs many times a day may need a much shorter limit.

After you change retention settings, it may take up to 12 hours before adTempus starts removing old records, and the removal may take several hours if there is a large amount of data.

Make sure you are running the latest version of adTempus, as newer versions often include database performance improvements.

Useful Queries

You can run these queries using the Database Utility or another query tool.

Database size (replace databasename with the name of the adTempus database):

sp_helpdb [databasename]

Space used by each table: choose "Show sizes for all tables" from the Snippets menu in the Database Utility.

Total number of job instances, and the number for each job:

select count(*) as TotalInstances from ExecutionHistoryItem;

select j.oid as JobOID, g.name as GroupName, j.Name as JobName, count(*) as InstanceCount
from job j join jobgroup g on j.jobgroupoid=g.oid
join executionhistoryitem ehi on j.oid=ehi.joboid
group by j.oid, g.name, j.name order by count(*) desc;

Total number of log messages, and the number for each job:

select count(*) as TotalLogMessages from LogMessage;

select j.oid as JobOID, g.name as GroupName, j.Name as JobName, count(*) as MessageCount
from job j join jobgroup g on j.jobgroupoid=g.oid
join logmessage lm on j.oid=lm.joboid
group by j.oid, g.name, j.name order by count(*) desc;

If a job has a very large number of log messages, check whether its scripts log routine messages that could be sent to the Script Debug Log instead (see Message Logging).

SQL Server Memory Usage

The SQL Server process (sqlservr.exe) may use a large amount of memory. This is normal: SQL Server uses memory that is not needed by other applications for caching, and releases it when other applications need it. High memory use by SQL Server does not by itself affect system performance.

If you want to limit the memory SQL Server uses, set the "max server memory" option using SQL Server Management Studio or by running the following commands in the Database Utility (this example sets the limit to 512 MB; we do not recommend a lower value):

EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure 'max server memory', '512'
RECONFIGURE WITH OVERRIDE