cyanes (4/1/2015)
I have a basic load proc (loading a staging area.) that truncates a target table just before inserting fresh data in said table, all wrapped in a distributed transaction. What happens (only with certain tables and very inconsistently.) is the truncate will fail (no error) and duplicate data ends up in the target table since no wipe actually occurs. This proc only started having this issue when we went from SQL 2008 to SQL 2014.Ideas, notions, thoughts?
Thanks in advance!
You mentioned that the truncate and insert are wrapped in a "distributed transaction". If you're attempting to truncate a remote table, you can't do this:
truncate table server1.database.schema.table;
But you can do this:
exec('truncate table database.schema.table') at server1;
There are 100 possible reasons this could fail, but the actual error message should indicate why. If you're getting no error, then perhaps you think you're truncating the remote table, but somehow you're inadvertently truncating a table with the same name on the local server.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Nothing that involved or complicated is happening with this proc, it is as simple as simple gets. I did make use of a Try/Catch block, sending error data to a local table (verified that this works as expected before putting it in place, used the divide by zero trick. lol). Was able to witness the failed Truncate and nothing was trapped. 
I suspect I am seeing a lock on the table as another contributor mentions. So I am moving down that avenue now.
Thanks!
Just in case, another option would be to set up a trace and look for the stored procedure, errors, etc. while the truncate runs. To me, it also sounds like a lock somewhere.
Also, another thought, depending on the size of the table, you can check prior to the truncate to see if something is accessing that table and then error out/quit if there is. I've had a lot of issues in the past with deletes/truncates with automated jobs. If it's a heavily used table, it's especially problematic.
JoshDBGuy,
The solution of checking for a lock is precisely where I was today, also I will be changing the design of this staging area. Planning to move it off onto it's own server then have our production do a merge pull on it. That is the end game change that I think is a better solution at this point.
Boy are you right, this has been very problematic.
Thanks for confirming my thinking and sharing your insights. 
Just a quick note I found on Books Online :
https://msdn.microsoft.com/en-us/library/ms177570.aspx
Have a look at the bottom section named restrictions. I see a mention of replication, might this apply to your table?
----------------------------------------------------
The table in question does not fall within any of those restrictions. Thanks for the feedback!
Consider setting up an audit event, DDL trigger, or profiler trace to keep track of when exactly a truncate operation is performed on this table. The simplist explanation is that the table isn't truncated; perhaps the task is bypassed, or perhaps the connection string is somehow pointing to a different environment.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
If you are working inside of SSIS, make certain there are no event handlers behind the scenes overriding what you are trying to do.
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply