Register SQL Service Instance: Timeout Expired

  • 15 February 2022
  • 0 replies
  • 2 views

Userlevel 4
Badge +16




 








Register SQL Service Instance: Timeout Expired





























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.










0 replies

Be the first to reply!

Reply