Share via

Schema Compare Execution timeout in VS 2017

  • Article

Question

Wednesday, October 18, 2017 11:42 AM | 1 vote

Hi. I have an issue in VS 2017 during schema compare tool i got an exception "Unexpected exception caught during population of source model: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

I found workaround how to increase timeout for previous versions of Visual Studio, but it isn't suitable for 2017.

Is there any way to increase execution timeout for schema compare?

All replies (4)

Thursday, October 19, 2017 7:33 AM

Hi Valery,

Thank you for posting in MSDN forum.

This forum is discussing Visual Studio Setup and Installation, since your issue related Schema Compare Execution timeout, I help you move this case to SQL Server > SQL Server Data Tools to get a better help.

Thanks for your understanding

Best Regards,

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Wednesday, October 10, 2018 9:12 AM | 4 votes

While surfing the interweb tubes I found this resolution that fixed my same issue.

  1. Close VS 2017 or you will get a file in use error
  2. Open regedit
  3. Choose HKEY_LOCAL_MACHINE, click "File" and select "Load Hive", paste this address: "%LOCALAPPDATA%\Microsoft\VisualStudio\
  4. Select the folder "15.xxxxxx" - I am guessing the extension differs by install
  5. Select/Open "privateregistry.bin"
  6. Use something like "Vs2017Hive" in the dialog box and click ok.
  7. Expand - HKEY_LOCAL_MACHINE > Vs2017Hive > Software > Microsoft > VisualStudio > 15.xxxxxx > SQLDB > Database
  8. Edit Key - "QueryTimeoutSeconds" setting the value to 600 or 6000... or what you like.
  9. Select File and choose Unload Hive - if you dont unload VS will error on opening 

When trying the sync again it should work

Wednesday, October 10, 2018 10:03 AM | 1 vote

Try updating stats on the system tables (https://www.dbdelta.com/sql-server-system-table-statistics-update/) rather than increase the timeout. Below are scripts to do that:

--use XML PATH for SQL 2016 and earlier
DECLARE @SQL nvarchar(MAX) =
    (
        SELECT 
              N'UPDATE STATISTICS ' 
              + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) 
              + N'.' 
              + QUOTENAME(OBJECT_NAME(i.object_id)) 
              + N';'
        FROM sys.indexes AS i
        JOIN sys.partitions AS p ON
            p.object_id = i.object_id
            AND p.index_id = i.index_id
        WHERE 
            OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
            AND i.index_id > 0
            AND p.rows > 0
        FOR XML PATH(''), TYPE).value('.','nvarchar(MAX)'
    );
EXEC sp_executesql @SQL;


--use STRING_AGG() in SQL 2017 and later, including Azure SQL Database
DECLARE @SQL nvarchar(MAX) =
    (
        SELECT
              STRING_AGG(
                  N'UPDATE STATISTICS ' 
                + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(i.object_id))
                + N';'
            ,'')
        FROM sys.indexes AS i
        JOIN sys.partitions AS p ON
            p.object_id = i.object_id
            AND p.index_id = i.index_id
        WHERE 
            OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
            AND i.index_id > 0
            AND p.rows > 0
    );
EXEC sp_executesql @SQL;

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Friday, November 9, 2018 7:12 PM

Thanks Dan , 

This worked for us. We are able to perform schemacompare form VS2017 now. 

--use XML PATH for SQL 2016 and earlier
DECLARE @SQL nvarchar(MAX) =
    (
        SELECT 
              N'UPDATE STATISTICS ' 
              + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) 
              + N'.' 
              + QUOTENAME(OBJECT_NAME(i.object_id)) 
              + N';'
        FROM sys.indexes AS i
        JOIN sys.partitions AS p ON
            p.object_id = i.object_id
            AND p.index_id = i.index_id
        WHERE 
            OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 1
            AND i.index_id > 0
            AND p.rows > 0
        FOR XML PATH(''), TYPE).value('.','nvarchar(MAX)'
    );
EXEC sp_executesql @SQL;