Registering an SQL Service Instance eventually times out:
"VALIDATION Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
This generally occurs when there are too many objects (tables, stored procedures and views) within the custom database. K2 will try to create ServiceObjects for every object in the database it has access to.
It is recommended to limit the permissions the authenticated user has on the database so K2 will not have to create ServiceObjects for every object it discovers. This is listed under the Considerations of the SQL Server Service Type.
Running an SQL Profiler trace shows the following queries being run against the custom database when registering a SQL Service Instance as the authenticated user:
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, CASE ( SELECT MAX ( CASE so.xtype WHEN 'PK' THEN 1 ELSE 0 END) FROM sysobjects AS so WHERE so.parent_obj = OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) ) WHEN 1 THEN 'yes' ELSE 'no' END AS [hasKey] FROM INFORMATION_SCHEMA.TABLES AS tbls WHERE TABLE_TYPE = N'BASE TABLE' AND [TABLE_SCHEMA] NOT IN (N'cdc') AND [TABLE_NAME] NOT IN (N'sysdiagrams', N'systranschemas', N'__RefactorLog') ORDER BY TABLE_SCHEMA, TABLE_NAME;
- Stored Procedures
SELECT DISTINCT SPECIFIC_SCHEMA, SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'PROCEDURE' AND [SPECIFIC_SCHEMA] NOT IN (N'cdc') AND [SPECIFIC_NAME] NOT IN (N'sp_alterdiagram',N'sp_creatediagram',N'sp_dropdiagram',N'sp_helpdiagramdefinition',N'sp_helpdiagrams',N'sp_renamediagram',N'sp_upgraddiagrams') ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME;
SELECT TABLE_SCHEMA, TABLE_NAME, IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS WHERE [TABLE_SCHEMA] NOT IN (N'cdc') ORDER BY TABLE_SCHEMA, TABLE_NAME;
Execute each of the queries above and take note of the execution time. You will need to take the total duration (in seconds) into account when setting the base value for the Command Timeout setting.