September 12, 2014 at 5:41 am
Iulian -207023 (9/12/2014)
I found this, on msdn, it must be on a different context and that would explain it or it can be a typo as well, who knows ... not so interesting to me, I understand what minimal logged means now. Thanks for that.It says:
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
what I would be curious is: what triggers are supposed to fire ?
Oh look. Books Online is wrong. Again.
Truncate does not fire triggers. It is however pretty easy to test and see that it is logged. There are no such things as unlogged data modification operations in SQL.
It is also true that Truncate cannot be used on a table that's marked for replication. That's a limitation of replication though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2014 at 5:50 am
Koen Verbeeck (9/12/2014)
Replication doesn't need triggers
Merge does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2014 at 5:52 am
GilaMonster (9/12/2014)
Koen Verbeeck (9/12/2014)
Replication doesn't need triggersMerge does.
Right. I stopped reading after transactional replication.
The sentence right after says merge replication uses triggers. :blush:
Thanks for the correction.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 12, 2014 at 6:18 am
This was removed by the editor as SPAM
September 12, 2014 at 6:36 am
Stewart "Arturius" Campbell (9/12/2014)
Iulian -207023 (9/12/2014)
GilaMonster (9/12/2014)
Meow Now (9/11/2014)
I was asked this very thing in an interview a while back. Except, they wanted to know if a TRUNCATE was fully or minimally logged. While I mentioned that all of the data could be rolled back with the use of a transaction, there was no way to roll back individual records...so minimally logged was my answer. Their follow up question was whether or not the recovery model would make any difference. I said no, but they just stared at me like I was crazy.It's fully logged.
A minimally logged operation is one where the logging behavior changed between the recovery models, hence both their follow up question and reaction to your answer makes sense.
Minimally logged operations are all insert-based (select into, bulk insert, bcp in, etc)
I found this, on msdn, it must be on a different context and that would explain it or it can be a typo as well, who knows ... not so interesting to me, I understand what minimal logged means now. Thanks for that.
It says:
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
what I would be curious is: what triggers are supposed to fire ?
Cheers,
Iulian
Suppose you have table dbo.x, with any type of trigger on it.
If you execute a truncate table dbo.x, the relevant trigger on table x will not execute.
Remember, TRUNCATE TABLE is a DDL command, not a DML.
OK I got it now. yes Truncate sounds very much like a DDL statement and it makes sens not to fire triggers.
September 12, 2014 at 7:36 am
That does make sense. I always knew it was a DDL and not a DML operation, I just never really gave much thought to the definition of fully vs minimally logged.
Aigle de Guerre!
September 12, 2014 at 8:13 am
This was removed by the editor as SPAM
September 12, 2014 at 11:24 am
Stewart "Arturius" Campbell (9/12/2014)
Iulian -207023 (9/12/2014)
OK I got it now. yes Truncate sounds very much like a DDL statement and it makes sens not to fire triggers.Just to demo, attached is a script that clearly shows the effect of TRUNCATE TABLE on a trigger.
Sweet - a demo with which to learn.
Thanks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 12, 2014 at 1:07 pm
This was removed by the editor as SPAM
September 12, 2014 at 1:21 pm
Koen Verbeeck (9/11/2014)
Please don't say truncate table data isn't logged unless you understand the concepts involved.😉
Allow me to rephrase that.
Please don't say truncate table data isn't logged.
(The second part of your sentence is actually redundant; if you understand the concepts involved you would never say such a thing.)
September 12, 2014 at 1:46 pm
Stewart "Arturius" Campbell (9/12/2014)
Iulian -207023 (9/12/2014)
OK I got it now. yes Truncate sounds very much like a DDL statement and it makes sens not to fire triggers.Just to demo, attached is a script that clearly shows the effect of TRUNCATE TABLE on a trigger.
I can see it , thanks a lot for putting together the demo
It first logs the inserts into AuditTrail , then truncate the table and rollback
All the time , I mean
before the transaction begin,
after the truncate table inside the transaction
and after rollback
the AutditTrail shows the same content.
I think that proves the Truncate Table does not fire trigger.
But I have one doubt that I would be curios about, and that is
Here I think we have DML triggers, while Truncate Table is a DDL statement so what if we make a DDL trigger and see if that one is fired.
but this is just a curiosity and is weekend here iepyyy iee 🙂 so we shall pick it again on another occasion, maybe next week.
I have to admit this is a lovely talk.
Have a nice weekend!
Iulian
September 12, 2014 at 2:58 pm
This was removed by the editor as SPAM
September 15, 2014 at 6:31 pm
GilaMonster (9/12/2014)
It is also true that Truncate cannot be used on a table that's marked for replication. That's a limitation of replication though.
It's a particular case of a more general statement (at least one that was true for SQL 2000 and I believe it's still true): replication replicates DML statements, and DDL statements on replicated objects are either forbidden or have special forms and/or restrictions.
It's an interesting thing in that it demonstrates one of (i) that SQL Server isn't a Relational DBMS at all or (ii) that SQL Server fails in some respects to be fully relational or (iii) that Replication is impossible in a relational DBMS, depending on how one interprets "relational". In my view, anyone who believes in (iii) is an idiot, while anyone who believes in (i) is a pedant; personally I believe that (ii) is the only sane position, but of course if one wants to be pedantic (or to clown, something I sometimes can't resist) (i) is clearly true as well (just look at sum of empty set and sum of set including both nulls and non-null values).
Tom
September 15, 2014 at 6:39 pm
Stewart "Arturius" Campbell (9/12/2014)
Investigate the impact of a truncate table statement and it's interaction with a DDL trigger and post the results as either a QotD or an article.
That's easy. None whatsoever.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2014 at 6:44 pm
TomThomson (9/15/2014)
replication replicates DML statements, and DDL statements on replicated objects are either forbidden or have special forms and/or restrictions.
It's got better. Alter table replicates now. Drop table isn;t allowed, the table has to be removed from the publication first and newly created tables aren't added (good reasons there at least). 1 out of three isn't bad.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply