Truncate table failing (intermittently), no errors presenting.

Question

Truncate table failing (intermittently), no errors presenting.

cyanes

Old Hand

Points: 388
More actions
April 1, 2015 at 10:43 am

#299246

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!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eric M Russell SSC Guru Points: 125522 More actions · Answer 1

If the procedure uses BEGIN TRY / BEGIN CATCH error handling, then confirm what it's doing with trapped errors. Consider commenting out the error handling blocks and just let it throw back the error so you can see it.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Snargables SSCrazy Eights Points: 8848 More actions · Answer 2

The one instance I was unable to truncate a table was when that table was referenced in an indexed view w schema binding. Are u guys using indexed views? U can't truncate a table that is in and indexed view w schema binding.

Snargables SSCrazy Eights Points: 8848 More actions · Answer 3

Snargables

SSCrazy Eights

Points: 8848

April 1, 2015 at 2:57 pm

#1788142

But even then it would give an error

Lowell SSC Guru Points: 323504 More actions · Answer 4

pretty sure you can't truncate a table if it's referenced by indexed views or foreign keys, or views or functions that were created WITH SCHEMABINDING either.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

Still ought to see some sort of error. Maybe you're just getting a timeout while it waits for a lock to clear? What's calling the truncate statement?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mister.magoo SSC-Forever Points: 47068 More actions · Answer 6

Let's just confirm this is a local temporary table, with one #, not a global with two ## ?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Eric M Russell SSC Guru Points: 125522 More actions · Answer 7

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

cyanes Old Hand Points: 388 More actions · Answer 8

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!

joshdbguy SSCertifiable Points: 7707 More actions · Answer 9

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.

cyanes Old Hand Points: 388 More actions · Answer 10

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. 😀

MMartin1 One Orange Chip Points: 27768 More actions · Answer 11

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?

----------------------------------------------------

cyanes Old Hand Points: 388 More actions · Answer 12

The table in question does not fall within any of those restrictions. Thanks for the feedback!

Eric M Russell SSC Guru Points: 125522 More actions · Answer 13

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

MMartin1 One Orange Chip Points: 27768 More actions · Answer 14

If you are working inside of SSIS, make certain there are no event handlers behind the scenes overriding what you are trying to do.

----------------------------------------------------