Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TRUNCATE TABLE on Mirrored DB Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 11:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:12 PM
Points: 197, Visits: 550
I truncated a table in my mirrored training environment on Principle side.

From what I remembered, TRUNCATE TABLE [tablename] is not a logged opereation.

Will this cause problems on the Mirror side?

Thanks!
Post #1347360
Posted Monday, August 20, 2012 11:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 22,492, Visits: 30,190
TRUNCATE TABLE is logged. If you truncate the table on the principal side, it will get truncated on the mirror.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1347371
Posted Monday, August 20, 2012 12:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
Truncate table is a logged operation (there are no unlogged data modifications in SQL). It can be rolled back, it can be mirrored. It's technically even a fully-logged operation.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1347381
Posted Monday, August 27, 2012 1:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:28 AM
Points: 449, Visits: 1,831
smallmoney (8/20/2012)
I truncated a table in my mirrored training environment on Principle side.

From what I remembered, TRUNCATE TABLE [tablename] is not a logged opereation.

Will this cause problems on the Mirror side?

Thanks!


It is minimally logged operation but wrong to say that it is unlogged. Information is logged therefore it can even be rolled back if done inside a transaction where commit was no issued and a rollback was initiated due to some reason.

Chandan
Post #1350241
Posted Monday, August 27, 2012 2:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
Technically it's not minimally logged. It doesn't behave as minimally logged operations do. It's fully logged, just as drop table is fully logged. Both of them log just the page deallocations.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1350242
Posted Monday, August 27, 2012 2:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:28 AM
Points: 449, Visits: 1,831
GilaMonster (8/27/2012)
Technically it's not minimally logged. It doesn't behave as minimally logged operations do. It's fully logged, just as drop table is fully logged. Both of them log just the page deallocations.


Thanks for using correct words for this situation and correcting my glitches.

Chandan
Post #1350247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse