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 ««123»»

Replication Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 7:25 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:00 PM
Points: 21,212, Visits: 14,910
Hany Helmy (1/22/2014)
Forgot it, so I had to make some RnD to get it right.


Nice approach. That's the kind of response I like to see.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1533624
Posted Wednesday, January 22, 2014 9:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:38 AM
Points: 1,780, Visits: 582
nice and easy..
Thanks Steve.
Post #1533727
Posted Wednesday, January 22, 2014 10:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:41 AM
Points: 309, Visits: 279
Thanks for the easy question Steve.
Having worked a lot with all types of replication this might have been easier to answer for me that the 45% that have not found it so easy.
Post #1533757
Posted Thursday, January 23, 2014 1:06 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 8,556, Visits: 9,047
Interesting question. I thought it worked for all models, but ir's so long since I last looked at replication that I thought I might be wrong, so checked in MSDN. The page I checked was The Transaction Log, which seems to say that log records are retained until the data has been replicated only in full recovery model, which seems to me to imply that transactional replication will not work in either simple or bulk-logged model, so (thinking that MSDN was more likely to have that right than my memory) I picked that answer - the wrongest one of the three.

Is that page wrong, or do the other recovery models have some method of delaying the discard of log records pending replication other than by setting log_reuse_wait to 6 in the affected records? Or perhaps in this question "replication works" means "snapshot replication works" (snapshot uses value 7, not value 6, to delay truncation)?


Tom
Post #1534234
Posted Thursday, January 23, 2014 1:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 5,916, Visits: 8,168
L' Eomot Inversé (1/23/2014)
Interesting question. I thought it worked for all models, but ir's so long since I last looked at replication that I thought I might be wrong, so checked in MSDN. The page I checked was The Transaction Log, which seems to say that log records are retained until the data has been replicated only in full recovery model, which seems to me to imply that transactional replication will not work in either simple or bulk-logged model, so (thinking that MSDN was more likely to have that right than my memory) I picked that answer - the wrongest one of the three.

Is that page wrong, or do the other recovery models have some method of delaying the discard of log records pending replication other than by setting log_reuse_wait to 6 in the affected records? Or perhaps in this question "replication works" means "snapshot replication works" (snapshot uses value 7, not value 6, to delay truncation)?


When replication is used, the log is not reused even when you are in simple recovery. Once replication has catched up, it will be available for reuse. (In full recovery, log records will not be reused until both the log backup and replication have catched up).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1534243
Posted Thursday, January 23, 2014 1:40 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 4,386, Visits: 3,395
Simple, straightforward, nice. Thanks, Steve!
Post #1534250
Posted Friday, January 24, 2014 11:37 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 8,556, Visits: 9,047
Hugo Kornelis (1/23/2014)
L' Eomot Inversé (1/23/2014)
Interesting question. I thought it worked for all models, but ir's so long since I last looked at replication that I thought I might be wrong, so checked in MSDN. The page I checked was The Transaction Log, which seems to say that log records are retained until the data has been replicated only in full recovery model, which seems to me to imply that transactional replication will not work in either simple or bulk-logged model, so (thinking that MSDN was more likely to have that right than my memory) I picked that answer - the wrongest one of the three.

Is that page wrong, or do the other recovery models have some method of delaying the discard of log records pending replication other than by setting log_reuse_wait to 6 in the affected records? Or perhaps in this question "replication works" means "snapshot replication works" (snapshot uses value 7, not value 6, to delay truncation)?


When replication is used, the log is not reused even when you are in simple recovery. Once replication has catched up, it will be available for reuse. (In full recovery, log records will not be reused until both the log backup and replication have catched up).

Yes, but how is release of the log records prevented? If the log_reuse_wait attribute in sys.databases is not set to 6 and there is no other value that indicates that transactional replication requires the log not to be truncated (value 7 is explicitly for snapshot creation, so not relevant to transactional replication except possibly while an initial snapshot is being created, and nothing else seems remotely relevant) it looks as if some method is used that doesn't use that attribute, so it seems that it can't be this attribute being nonzero that tells the system that it can't recover any virtual log files. Maybe simple and bulk logged recovery models set it to 1 when no VLF can be retrieved because of transactional replication?


Tom
Post #1534608
Posted Friday, January 24, 2014 11:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 5,916, Visits: 8,168
L' Eomot Inversé (1/24/2014)
Yes, but how is release of the log records prevented?

My guess is that the information on that page you used is incorrect. But I have never worked with a replicated database in simple recovery, and I am too lazy to set up a test scenario now.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1534614
Posted Saturday, January 25, 2014 12:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 8,556, Visits: 9,047
Hugo Kornelis (1/24/2014)
L' Eomot Inversé (1/24/2014)
Yes, but how is release of the log records prevented?

My guess is that the information on that page you used is incorrect. But I have never worked with a replicated database in simple recovery, and I am too lazy to set up a test scenario now.

Sounds like a reasonable guess to me. So maybe in future I'll trust my memory and not check in BOL even when it's something I last looked at a long time ago.


Tom
Post #1534738
Posted Monday, January 27, 2014 1:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Good one! help to sharpen Replication skill... recalling old memories :)

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1534883
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse