An error was received from SQL Server while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPrimaryKeyConstraint: Execution Timeout Expired.

Question

While there are some similar questions:

1) This is with a local SQL Server 15.0.2000.5 running on Windows Server 2022 Standard (10.0) [so discounting Azure specific item]
2) This happens from VS-2019 and VS-2022 [so discounting any "VS is now 64 bit items"]
3) This happens with multiple (albiet similar) Databases [so minimizing corruption thoughts]
4) I have rebuild the statistics for the query plans...
5) The queries (as per profiler capture) seem to execut and complete, taking approx 1.5 minutes - error occurs after that (as if procesing the data).
6) There are 16K to 20K Primary Key constraints in the DB

Really need this to isolate some critical issues, so any ideas quite welcome.

Answer 1

I suggest updating statistics on system tables to avoid SSDT query timeouts. Here's a script to update system table stats.

DECLARE @SQL nvarchar(MAX) =  
(  
     SELECT  
         STRING_AGG(  
               N'UPDATE STATISTICS '   
             + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))  
             + N'.'  
             + QUOTENAME(OBJECT_NAME(i.object_id))  
         ,';')  
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;  

Answer 2

Hi @David Corbin ,
Firstly you can check if the timeout is set as 0:

Secondly , you can try the two queries:

exec sp_updatestats  
  
dbcc freeproccache  

Besides you can check your query see if you can optimize. The timeout can happen for many reasons, pls tell us the reflection after your using the two ways.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"