This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice This article is not considered official documentation for K2 software and is provided “as is” with no warranties.

Issue

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."

Image

Symptoms

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.

Troubleshooting Steps

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:

  1. Tables
    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;
  2. 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;
  3. Views
    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.