Are seeing slow performance or frequent timeouts (e.g. errors like “execution canceled”) when using FastMaint with an external SQL Server database? Here are a few causes we have seen at customer locations.
1. Open the database in SQL Server Management Studio. Check that the database recovery mode is Simple (the default SQL Server setting). Other modes can generate ever growing log files that finally fill up all the disk space on the server unless someone truncates the logs regularly.
2. If you originally created the database on SQL Express edition turn off AutoClose. Databases created on SQL Server Express are set to AutoClose ON by default.
3. Make sure that FastMaint is using a SQL Server user account that has DDL administration as well as read+write access to the FastMaint database. Set the default schema for this user account to “dbo” (database owner). Otherwise you may get errors and reports of missing tables or objects in FastMaint. In SQL Server Management Studio you should see all tables in the FastMaint database have the prefix “dbo.” e.g. “dbo.sm_task”, “dbo.sm_vendor”, etc.
4. Check the memory utilization and CPU usage on the database server. Ideally memory usage should be 60% or less and CPU usage should average less than 10% (with occasional spikes). Make sure you do not have other open applications running on the server. For example having a running instance of Google Chrome left open on the server is know to cause large memory and CPU usage.
TIP: Using a UDL file to test SQL Connectivity
Want to test your connection to a local/ remote SQL Server instance but you do not want to install the SQL tools just to make that test? You can create an empty UDL file. This will bring up a window to allow testing of connections to a SQL Server instance. To create this file use any folder on your system and create an empty text file. Give it some name (e.g. TestSQLConnection.udl). Instead of the default TXT extension make sure that the extension is UDL. Now double-click on the file. Windows will pop up a SQL connectivity window to allow testing of SQL connections. Note that you should use the Microsoft Windows 32-bit ODBC Manager to test ODBC connections used by FastMaint Professional since the UDL file does not support ODBC.