Dumb Question

Question

Dumb Question

Viewing 15 posts - 31 through 45 (of 46 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

GSquared (7/23/2008)
I'm looking at the BOL data on DDL triggers, and I don't see Truncate Table as one of the actions. Am I overlooking it?

Yep, you are correct, Gus. Here it is straight from microsoft:

Known issues of DDL Trigger in SQL Server 2005
• Truncate statement is not tracked by any event
• SP_Rename event is not tracked

Wow, that's quite a hole...

(I know that rename can be tracked in 2008, but I haven't checked on Truncate)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 2

Thanks. Was pretty sure I wasn't missing it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

rbarryyoung (7/23/2008)
1) Recovery: Audit table is supposed to serve as a means to reconstruct a pre-existing table/database. Audit table is stored in a different DB & disk or is remote; in th eevent of an unauditable failure (Truncate, DB loss, etc.), the Audit table can be used to reconstruct the table(s).

Does transactional replication replicate TRUNCATEs? If yes, then I have to eat a fair bit of crow and say you are correct on all 3. :blush: Thanks for the 3 examples. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jeff Moden (7/23/2008)
Does transactional replication replicate TRUNCATEs? If yes, then I have to eat a fair bit of crow and say you are correct on all 3. :blush: Thanks for the 3 examples. 🙂

Good question. I confess I have no idea, but from everything else that I have seen I would guess that it cannot, since none of the facilities that it uses seem to be able to catch Truncate either.

I also confess that I am not sure how transactional replication plays into this?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

If transactional replication does not replicate truncations, then there is no need to audit inserts to rebuild a truncated table because you can always get it back from the replicated data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

hmmm (scratches head) I guess I missed that transactional replication was involved.:ermm:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

rbarryyoung (7/23/2008)
hmmm (scratches head) I guess I missed that transactional replication was involved.:ermm:

Heh... I missed that data marts were involved. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 8

I just set up a mirror on one of my proof of concept databases, then truncated a table (that did have rows in it), then did a failover on the mirror, and queried the table in what used to be the mirror. Table was empty.

From that, I'm going to say that replication includes truncation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

Just double checking... Was the mirror made using SQL Server Replication or (disk) hardware replication.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 10

SQL Server. Did it with the wizard in Management Studio.

Disk mirroring wouldn't be a test of the truncate command.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 11

This wasn't database mirrroring was it? I believe replication and mirroring, while similar, function differently.

Truncate not being tracked in DDL triggers is a problem. I hope this is fixed in XEvents in 2008

GSquared SSC Guru Points: 260824 More actions · Answer 12

What I tested was mirroring, but transactional replication would do the same thing.

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.

(BOL)

Since truncate commands go into the transaction log, and replication, "copies the transactions ... into the distribution database", using the logs, it should copy it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 13

I was wrong.

Just tested it. Transactional replication from one instance to another. Updates, inserts, deletes, all transfer successfully (and immediately).

Truncate gets this error:

Msg 4711, Level 16, State 1, Line 1

Cannot truncate table 'dbo.logtest2_log1' because it is published for replication.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

GSquared (7/25/2008)
I was wrong.
Just tested it. Transactional replication from one instance to another. Updates, inserts, deletes, all transfer successfully (and immediately).
Truncate gets this error:
Msg 4711, Level 16, State 1, Line 1
Cannot truncate table 'dbo.logtest2_log1' because it is published for replication.

Outstanding, Gus... Thanks, for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 15

Steve Jones - SSC Editor

SSC Guru

Points: 736162

July 25, 2008 at 7:08 pm

#848740

Yep, thanks for the update. Good to know that.