Whether it is via the Content Matrix console or through PowerShell, every time a Content Matrix job is run, detailed log entries are written to the nominated database. Over time, the increasing volume of detailed job log entries can lead to very large databases being used to store this jobs information, and the responsiveness of the database will start to degrade over time. The following errors may get thrown intermittently:
- Error running jobs: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- The underlying provider failed on Open. The timeout period elapsed prior to obtaining a connection from the pool.
A) If SQL CE is used to store Jobs database:
Quest does not recommend the use of SQL CE databases to store jobs logs for production migrations. If you are expecting to be performing larger migrations, you should immediately switch to using a MS-SQL database to store your jobs data. This is something recommended to all customers.
B) If MS-SQL is used to store Jobs database:
Whether using a distributed database, or a stand-alone jobs database, customers performing large migrations can still expect to see performance impacts as their jobs database increases in size. Resolutions to consider include:
1) Clean up existing database:
After taking appropriate steps to back-up the database and/or export the jobs data, you could purge some or all rows from the JobLogItems table in the database you use to store your jobs data. The advantage of this is that you retain all your configured jobs in the jobs database if you need to refer to them at a later date. Another advantage is that you will retain your agent configurations.
2) Use new database:
As your planned migrations progress, at a suitable time you could create a new database and switch over to using this. The advantage of doing this is that you can still access all the data in the original database if necessary, but it does not impact the use of the newly created database.
3) Assign additional resources for the database:
As a short-term solution only, you could gain some temporary minor improvements by assigning additional resources to the database being used to store your jobs details.
Note: Consult your DBAs to run SQL Profiler to monitor the performance of your SQL database.