I have one doubt that I want to clear out by help of your thinking and reasoning.
A stored procedure (SP) was running for ages on SQL 2005. Its aim is to fill a table DDB. All the tables used in SP are in same database. The collations of all tables including collations for theirs columns and the database collation are identical. The collation of the server instance is also identical. The SP usually inserts about 1 million records on daily basis.
Some days ago the SP began failing with error message “…PK constraint violation … for table DDB …”.
All the FK relations are checked and there are no any problems. No triggers , no constraints on the table. Only one primary key.
ANSI NULLs = True
Quoted identifier = True
Collation = SQL_Latin1_General_CP1_CI_AS
There are no any special settings on the database or on the server. It happened on a local machine as well as on the production.
There are no duplicate records in tables used, or it never happens to try to insert a record with a key that already exists in table DDB. Additionally, I checked all the tables used by the sp to ensure there are no duplicate records (not necessary this, but checked for any case) which may possibly be candidates to insert or to take part in insert operations and make some issues.
The stored procedure uses #temp tables (5 tables) to store data temporarily. The collation of the tempdb database is same as the current database. The collations of the #temp tables that are created during sp execution also have the same collation as the current database.
The admins upgraded SQL 2005 to SQL 2008R2.
The problem on SQL 2008R2 is similar. The SP firstly never ended. I created some indexes on some tables, and updated statistics for all tables and then appeared the same problem as above on SQL 2005. And I did the same checks as above for FK, collations, duplicate possible inserts and it didn’t work.
I run DBCC CHECKTABLE , DBCC CHECKDB , and they passed well without any errors reporting.
I have resolved the problem, but the issue now is that I have doubts for my solution. What I did was replacing the #temp tables within the sp with real-temp tables – tables that now exist in the databases and are re-created on every SP run.
One of the questions is why it worked so long time with #temp tables, and now stopped?
With my thoughts for this issue I’m now a bit more in the admin area, so expect some such discussions and thoughts … So guys, can you guess, discuss or explain something on this case that is not clear to me. I will try to reconstruct the problem and try again with more analysis on this.
My blog: www.igormicev.com