TFS stored procedure and other TFS related SQL calls might fail during TFS Configuration if the query governor is enabled and has a low threshold defined in it.

Problem Description:- I came across a customer who is not able to configure TFS correctly because TFS Configuration database would fail to create. We looked in the profiler trace and we are seeing the SQL server throwing the following error while TFS tries to configure the configuration db: “The query has been canceled because the estimated cost of this query (802) exceeds the configured threshold of 700. Contact the system administrator.”

Cause:- SQL Server DBA had enabled the query governor and set a threshold for it. You can confirm this:
From https://msdn.microsoft.com/en-us/library/ms191219(v=sql.90).aspx:

1.       Launch your SQL server Management Studio, In Object Explorer, right-click a server and select Properties.
2.       Click the Connections page.
3.       Select or clear the Use query governor to prevent long-running queries check box.
If you select this check box, in the box below, enter a positive value, which the query governor uses to disallow execution of any query with a running length exceeding that value.

Resolution:- To fix the issue that we are seeing, uncheck the “Use query governor to prevent long-running queries” check box as shown above, restart SQL Server services and try configuring TFS again.
For SQL Server deployments that are integrated with TFS we normally keep the query governor disabled.

Content prepared by: Gaurav Bodar

Content Reviewed by: Lakhminder Singh