Microsoft distributes Microsoft SQL Server 2012 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release.
Consider the following scenario:
You create one or more data quality projects in Data Quality Client in Microsoft SQL Server 2012.
You install Cumulative Update 1 for SQL Server 2012 (SQL Server 2012 CU1), and then you run the DQSInstaller.exe file to upgrade Data Quality Services (DQS) catalogs and to add new objects.
You try to delete one of the created data quality projects by using Data Quality Client.
In this scenario, Data Quality Client stops responding, and the SQL Server database engine enters an infinite loop. Additionally, the sqlservr.exe process experiences high CPU usage.
To recover from this issue, restart Data Quality Client and DQS.
This issue only occurs if the data quality project was created before you installed SQL Server 2012 CU1.
This issue occurs because of an infinite loop in a system stored procedure that is caused by updating a loop counter incorrectly.
Note This issue occurs because of a regression in SQL Server 2012 CU1.
Cumulative update information
Service pack information for SQL Server 2012
To resolve this problem, obtain the latest service pack for SQL Server 2012. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2755533 How to obtain the latest service pack for SQL Server 2012
SQL Server 2012
The fix for this issue was first released in Cumulative Update 2 for SQL Server 2012. For more information about how to obtain this cumulative update package for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
2703275 Cumulative update package 2 for SQL Server 2012
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released
To work around this issue, use one of the following methods.
To add a dummy procedure that enables the stored procedure infinite loop to continue and then exit correctly, follow these steps:
Run the following query to identify the schema name for the affected project that you tried to delete:
SELECT 'KnowledgeManagement'+CAST(ID AS NVARCHAR) AS SchemaName, Name FROM DQS_MAIN.dbo.A_KNOWLEDGEBASE WHERE TYPE=2
Use the SchemaName value that is returned, and then run statements that resemble the following in SQL Server Management Studio to supply the matching schema name and the Data Quality Project ID. A new dummy procedure will be created for the schema and the corresponding data quality project. Therefore, the infinite loop proceeds correctly.
USE DQS_PROJECTSGOCREATE PROCEDURE DQProject<ID>.NewIndexParseTrigramsFromLexiconAS BEGINPRINT 'THIS IS A DUMMY PROCEDURE TO MAKE DELETE WORK for Microsoft KB Article 2705571';END
Note <ID> represents the internal ID of the Data Quality Project that is being deleted.
Verify that the data quality project is deleted from Data Quality Client as expected.
If you have to kill the looping procedure quickly to conserve CPU resources, run the following statements to identify the correct session. However, killing the looping procedure does not delete the data quality project cleanly. The following Transact-SQL statements help identify and kill the correct session. If more than one session from Data Quality Client is running, you may have to run the statements multiple times:
DECLARE @session smallint, @text nvarchar(2000), @killcommand nvarchar(200)SELECT TOP 1 @session=s.session_id, @text=st.textFROM sys.dm_exec_sessions sFULL OUTER JOIN sys.dm_exec_requests r ON s.session_id=r.session_idCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS stWHERE program_name like 'DQ Services Client: Client Id = %, KB Id = %, Proxy = KnowledgebaseManagementEntryPointClient'AND s.status='running';SELECT 'Killing this DQS session:', @session SessionID, @text QueryText;SET @killcommand=N'Kill '+ cast(@session as nvarchar)+';'EXEC sp_executesql @killcommand;
Note If you use method 2 to kill the DQS connection, you may receive the following error message in Data Quality Client: