In this article
Automatic Detachment of Inactive Survey Databases
The traditional Forsta Plus database model operates with a separate database for each survey created on a site (two if a survey is compiled in test mode.) Although this provides a fully segregated database model and allows for individual maintenance of particular surveys, it would eventually become a problem if the server was loaded with more databases than it could effectively operate with in memory, potentially leading to an unstable server, and in the worst case, a server crash rendering all databases unavailable. This was not likely to occur until the number of databases reached over 1,000 unless running on database server with very little memory installed, but still something had to be done for the future of growing installations.
One of the issues that can be seen if the amount of databases on the system starts reaching the limit is errors when running certain tasks on the system, typically yielding errors like "There is insufficient memory to run this query". This warning indicates that the SQL Server's MemToLeave area is too small for the current amount of databases and transactions.
Microsoft SQL Server allocates at least one 64KB block used for formatting log records before they are flushed to disk. The allocation takes place when the first log record is generated for a database, following any INSERT, UPDATE or DELETE statement. Depending on the size of the generated log records and the activity on the system, additional 64KB blocks may, and will in most cases be allocated as well.
The situation can be somewhat relieved by configuring the SQL server to use the -g startup flag to instruct the server to leave more memory aside to prevent that the combination of log-related allocations and other allocations causes the SQL server to run out of virtual address space. The more databases are attached and active and on the system, the more memory would likely have to be reserved.
The Microsoft SQL Server Books online provides more documentation on this feature, and http://support.microsoft.com/kb/316749 references it in more detail as well.
Forsta Plus can detach survey databases that have not been accessed for a while. The [confirm_admin]..[surveydatabases] table stores and maintains information about the current status of every survey database on the system, as long as the project has not been deleted from the GUI. The table is initially populated during the installation of Forsta Plus, and is automatically updated with new rows when databases are compiled for new projects.
Included as a stored procedure in the [confirm_admin] database is usp_DeactivateIdleSurveyDatabases. The procedure takes one parameter; an integer specifying (in minutes) how long a database should be inactive before it's detached by the procedure, with 1440 (24hrs) as the default set up in a maintenance job created on the database server during Forsta Plus installation; "Detach idle surveydatabases". The job is always installed but not enabled by default, and must be enabled manually to run. It is recommended that it is enabled for most environments.
When the procedure runs, it will read through the [surveydatabases] table and store the LastConnect timestamp (which is updated dynamically by any Forsta Plus application module which uses the database in any way,) and detach any survey databases that exceed the set limit, thus freeing resources for the SQL Server process. It will also set the status of the database as being currently detached (inactive.)
If any part of Forsta Plus (a survey, report user, etc.) tries to access the database while it's detached, Forsta Plus will automatically reattach the database (provided the files are still available) and reset the status to attached (active.)
Important
The SQL Agent will reset NTFS permissions on the databases when you detach them. If a Windows user account performs the detach, only that user will have access to the file after the detachment is complete. Therefore the SQL Server Agent should run under the same user account as the SQL Server, otherwise your SQL Server will not be able to attach the databases. Alternatively, you can set up custom scheduled tasks to perform the appropriate ICACLS file permission grants so that the SQL Server account has access immediately after a detach job run.
If for any reason there are databases that should not be detached from the system, the [surveydatabases] table can be modified, altering the donttouch column for the relevant row for a specific database. Setting the value of this column to '1' for required databases will make the detach procedure to overlook them in the detachment process.
Important
To prevent excessive reads to the control table to determine survey database status, each server caches a survey database’s status for 60 minutes after a successful status check. Keep this in mind if detaching databases manually with a shorter time frame than 60 minutes, and recycle IIS on web servers and restart the Forsta Plus Task System on batch servers if there are inconsistencies – or simply wait for the issue to resolve itself within an hour.
Automatic Detachment of Inactive Hub Databases
In the System Configuration > Hub section, there is a setting called MinutesIdleTimeBeforeDetachingHubDatabase that is used by the Hub Cleanup task to determine the idle time for hub and vault databases. Any hub and vault databases that have been idle for longer than the minutes defined in this setting will be detached as the Hub cleanup task runs.
The Hub Cleanup task is not enabled by default; to enable it go to Admin> Maintenance > Hub Cleanup.