July 28, 2012 at 4:33 am
Dear All,
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.
Kind regards
IgorMi
Igor Micev,My blog: www.igormicev.com
July 28, 2012 at 8:23 am
As a first step I would like to suggest that you start a server side trace to see as to what is happening.
From the perspective of not capturing too much information and also not adding to much overhead to the server please add a filter so that it captures all events and errors only for SPID associated with stored procedure execution.
This will give you some sort of handle to start your troubleshooting with.
The positive thing is that you seem to be able to replicate this problem very easily and that means the solution is not far away. This is really a interesting problem and a great opportunity for learning something new.
July 28, 2012 at 11:41 pm
IgorMi (7/28/2012)
...or it never happens to try to insert a record with a key that already exists in table DDB
Despite your claim and based on the fact that you have no triggers (and I'm assuming no Indexed Views), the only thing that will give you a PK violation is an attempt to insert duplicate data.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 29, 2012 at 2:47 am
Despite your claim and based on the fact that you have no triggers (and I'm assuming no Indexed Views), the only thing that will give you a PK violation is an attempt to insert duplicate data.
I know it is hard to you to give as most precise answer as you could do, and me too 🙁
However I appreciate much.
If I come up with a clear explanation why it happened I'll surely reply on this thread.
select * from deposits_daily_balance ddb
where ddb.val_date_d = cast('2012-07-24' as datetime) or ddb.reg_date_d = cast('2012-07-24' as datetime)
-- 0 records
select ddb.num_cue_s, count(*)
from ddb_temp_DDB ddb -- or from #ddb_temp_DDB ddb
group by num_cue_s
having count(*)>1
--0 records
primary key is composed of num_cue_s,val_date_d and reg_date_d
declare @t_date_d datetime
set @t_date_d = cast('2012-07-24' as datetime)
INSERT INTO deposits_daily_balance
SELECT
tdb.num_cue_s,
@t_date_d AS val_date_d,
@t_date_d AS reg_date_d,
val_date_balance_n,
recalc_int_n,
matured_int_n,
matured_bonus_int_n,
OD_matured_int_n,
int_accrued_n,
bonus_int_accrued_n,
OD_int_accrued_n,
tdb.int_mes_n,
accrued_fee_n,
paid_maintenance_fee_n,
put_on_reservation_n,
out_of_reservation_n,
tdb.od_limit_n,
tdb.od_fac_l,
cor_reg_n,
tdb.branch_s,
tdb.account_definition_id_n,
tdb.estado_s,
tdb.fee_offbalance_l,
AMF_last_payment_date_d,
AMF_last_accrual_date_d
-- this does not work
FROM #ddb_temp_DDB tdb
where not exists (select 1 from deposits_daily_balance ddb_t where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
-- this does not work
-- this works
FROM ddb_temp_DDB tdb
where not exists (select 1 from deposits_daily_balance ddb_t where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
-- this works
Igor Micev,My blog: www.igormicev.com
July 29, 2012 at 10:13 am
Does this work ?
....FROM #ddb_temp_DDB tdb
where not exists (select 1 from deposits_daily_balance ddb_t
where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
COLLATE 'SQL_Latin1_General_CP1_CI_AS'
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 30, 2012 at 2:11 pm
Sachin Nandanwar (7/29/2012)
Does this work ?
....FROM #ddb_temp_DDB tdbwhere not exists (select 1 from deposits_daily_balance ddb_t
where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
COLLATE 'SQL_Latin1_General_CP1_CI_AS'
Unfortunately not.
Kind regards
IgorMi
Igor Micev,My blog: www.igormicev.com
July 30, 2012 at 2:20 pm
What do you mean by "it does not work?" You mean it never completes? I assume you have a create table statement for your temporary table or a SELECT INTO. Are you sure that statement is completeing? What is your entire script?
Jared
CE - Microsoft
July 31, 2012 at 5:45 am
SQLKnowItAll (7/30/2012)
What do you mean by "it does not work?" You mean it never completes? I assume you have a create table statement for your temporary table or a SELECT INTO. Are you sure that statement is completeing? What is your entire script?
"it does not work" means it finishes with constraint violation, and therefore no insertion is done.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
July 31, 2012 at 6:03 am
IgorMi (7/31/2012)
SQLKnowItAll (7/30/2012)
What do you mean by "it does not work?" You mean it never completes? I assume you have a create table statement for your temporary table or a SELECT INTO. Are you sure that statement is completeing? What is your entire script?"it does not work" means it finishes with constraint violation, and therefore no insertion is done.
Regards
IgorMi
Ok, what is the violation exactly? Can you please post the full script you are using (edited for confidentiality if need be). It is really hard to help you without the entire picture.
Jared
CE - Microsoft
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply