How to troubleshoot Error 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

Question: I’m investigating a persistent error message Error 2627 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls. How can I troubleshoot and fix? Answer: In summary , the unique index on the column is preventing the statement from commiting. Another record exists with the same value on the unique index column. Error 2627 is closely related to a constraint violation. Constraints are similar to Unique Indexes – when a UNIQUE CONSTRAINT is created a UNIQUE INDEX is automatically created. Read more on Difference between UNIQUE CONSTRAINT versus UNIQUE INDEX Violation... Read more →

Error 7983 - User '%.*ls' does not have permission to run DBCC %ls for database '%.*ls'

I receive SQL Server exception reports, filtered on exceptions based on severity. I noticed a high level of exceptions with the pattern: User '%.*ls' does not have permission to run DBCC %ls for database '%.*ls'. Error = 7983 Severity = 14 State = 36 I ran a sql server trace and spotted the exception when the login attempted to execute the the command: dbcc opentran ("myDB") with tableresults It was monitoring software running regularly on the SQL Server. I wasn’t surprised the software was attempting to execute this command. I checked through the documentation and confirmed I’d set up the... Read more →

Error 3621 - The statement has been terminated

Question: A client is sending a query to a SQL Server . After 30 seconds a message appears on the SQL Server trace: User error message : The statement has been terminated If I run the code from SSMS, it completes successfully. Could you explain how I can fix this problem? Answer: SQL Server runs queries with no timeout. The query timeout error is occurring on the client side. When a command exceeds the timeout duration, the client API sends an attention signal to SQL Server to cancel the executing query. An example is the CommandTimeout property, defaulting to 30... Read more →

How to improve SQL Server Execution Plan Reuse

Very useful topic to improve SQL Server performance Read more →

How to Find queries causing Lock Timeouts per second

Track down SQL queries causing Lock Timeouts per second Read more →