SQLDBAs


MY Current database in SIMPLE recoverey model

I have set up the transactional replication. MY log reader process wakes up and runs every 5 min. Does my transactions will get deleted or they still in log file as active even it is in simple recover model.

Logreader how it reads the Transaction log files . I observed today we are running some big reports. those reports uses INSERT INTO FROM <TABLE > this process showing in DBCC OPENTRAN

MY log reader keep on reading the log file.. it did not come out from that five min duration...

Can someone from the MS share the what /how exactly log reader works ( inside details)

I would like to add more details

The Log Reader Agent is scanning the transaction log for commands to be replicated. Approximately 30500000 log records have been scanned in pass # 3, 1696 of which were marked for replication, elapsed time -623164036 (ms).

The Log Reader Agent is scanning the transaction log for commands to be replicated. Approximately 31000000 log records have been scanned in pass # 3, 1713 of which were marked for replication, elapsed time -177869239 (ms).

everytiome counter startwith 500000 records and keegoing for some time and it is delivering the transactions never stopping the job

Why elpse time is in negative

How to understan

Do you guys think it is really sc

Thanks

Srini




Re: Log reader internal

Michael Hotek


No, they won't get deleted. When you have a database in simple recovery model, the committed transactions log are thrown away with each checkpoint. It does this by starting at the beginning of the log and reading sequentially forward until it hits the first open transaction. Once it hits the first open tansaction, it stops reading, goes to sleep, and is kicked off again with the next checkpoint.

When you turn on transactional replication, it obeys a secondary value in a transaction log record, basically a flag. This flag indicates that the row has been successfully written to the distribution database. Therefore, for rows associated to tables which are participating in transactional replication, the ONLY way the row can be removed from the transaction log (either simple recovery model, truncating the log, or log backup) is that both the flag that the row was committed AND that it was successfully written to the distribution database must be true.

How does the basics of the log reader work The log reader generally runs continuously, which means that it executes a cycle of the agent to look for changes and transfer them to the distribution database, log that it completed, and then starts the process over again. The basics of what it does are as follows:

1. Connect to the distribution database and grab the last LSN that was written to distribution (this is referred to as the replication watermark)

2. Traverse into the transaction log to the replication watermark

3. Start at the replication watermark and continue forward through the log looking for transactions that have been committed for tables participating in transactional replication

4. Write each of these rows into the distribution database,maintaining the proper commit sequence

5. When it hits the first open transaction, stop reading

6. Update the distribution database with the last LSN read (moves the replication watermark forward)

7. Go back into the log and flip the flag indicating it was successfully written to the distribution database

8. Repeat steps 1 - 7

There is a bit more that happens and there is some rather intricate code, but this is the basic process flow that occurs.







Re: Log reader internal

SQLDBAs

Mike Thank you soo much for internal information. this partial answer my question.

However, I have setup the Logreader Process to wake up at every 5 min intervele...However , This morning it started at 9:40 and keep running it never completed. However we are running few big reports which does , massive update / insert (at least 20 to 20 M) .. We are not using any begin tran .. commit trans how atranaction become an open transtion DBCC OPENTRAN. because of this in sys.databases logresuse flag set it REPLICATION. . When exactly a transction appears in DBCC OPENTRAN






Re: Log reader internal

Greg Y

May I ask why your logreader is running scheduled every 5 minutes instaed of continuously You shouldn't have to schedule it, the impact of logreader agent should be minimal to any OLTP activity on the published database.

Regarding never completing, can you describe your massive update/insert If you issue one update statement that affects 20 million rows, then that's considered one transaction. Logreader won't begin picking up transactions until the update completes, and when it completes, that's when it starts populating the distribution database. Have you verified that the distribution database is being populated with changes





Re: Log reader internal

Michael Hotek

To further expound on Greg's post. It doesn't matter if you aren't explicitly beginning and committing a transaction. They are still wrapped in a transaction. SQL Server will implicitly start a transaction as soon as you launch an insert, update, delete, etc. and then will implicitly commit that transaction when it completes. The instant that you either say begin transaction or you launch an insert, update, delete, etc. and get an implicit transaction, you will see it as an open transaction and will not be closed until an either an explicit or implicit commit is issued.




Re: Log reader internal

SQLDBAs

Mike/Greg's ,

Sorry for the late reply. I thank for your inputs. I would like to know one more thing..

How would we replicate Dataware house kind of databases.. because lot of reports are long running and they will have open transactions.. so, what is the best way to replicate the data . MY main intention is to use the other server for data processsing...

because fact tables data we load on one server same data needs to be replicated to the other server. .. I am having problem while reports are running at primary server I am having this log reader problem even I tried with continous option also...

Please provide some insights how would I go about this.





Re: Log reader internal

Greg Y

What kind of transactions are you talking about, queries or DML-type activitity like inserts, updates, deletes Assuming the latter, logreader will not replicate uncommitted transactions, so if it takes 10 minutes to execute/commit a large batch update transaction, the logreader will begin replicating after the txn completes. Reporting Server type activity should have nothing to do with logreader agent, unless it's competing for CPU/memory.

You can try adjusting your batch process by committing smaller-sized transactions. For example if transaction-size is 10 million commands, try breaking it up into smaller transactions, like 1 million or less if possible.





Re: Log reader internal

SQLDBAs

Greg Thank for the reply...

We do insert/update no delete.. Updatings the at least 30 to 45 million records. If ten batch jobs are runnign at same time they take updates / inserts they never completes OPEN TRANS . eg: IF job 1 done with 1 million records the other job may open the transactions same saga repeats . so, log is getting full...





Re: Log reader internal

Greg Y

As I mentioned, until the parent-most transaction is complete, logreader agent will not pick up the change. Doesn't matter if it's in continuous mode or not.



Re: Log reader internal

SQLDBAs

Greg , Then what is the best option for keeping DW databases in sync. I still want to use secondary database..

I tried to use SSIS to load the on both machine because of IDENTITY values we are having crappy issue. So, I went for replication and ran into these issues

Any other suggestion





Re: Log reader internal

Greg Y

Either I'm not understanding the problem correctly, or I don't know how DW scenarios work (which could be the case).

If you're tell me you always have a transaction open, which may not commit for a very very long time, what happens if the server goes down Do all your changes roll back, and then you have to start over

And just to be clear, can you tell me why it is you think it's the logreader agent that's the problem Are you saying it's not populating the distribution database at all Let's assume your transaction size is 45 million changes. Once that completes, log reader agent will begin scanning the log and then replicate approx 45 millions changes to the distribution database. Once that completes, distribution database will then pick up the changes and replicate them to the subscriber.

Using replication to replicate DW scenarios is very popular/common, but you need to be very specific as to exactly what the problem is, and where you think it is before we can help you.





Re: Log reader internal

SQLDBAs

Greg,

It seems like I haven't explained my problem properly..

Here is the case..

I have Server 1 With all fact table ( These tables loaded weekly by SSIS packages) . We would like to replicate these FACT table Data to SERVER 2. What's happening was While long running reports are keeping transactions open for long long time . LOGREADER unable to move further. However these tables data is not good for replication. ( These tables are temp for report purpose only). I replicate only fact not temp report tables. becaue of these OPEN tran LOGREADR UNABLE TO MOVE AND COMPLETE my transactions. Not sure why LOGREADER DOES HAVE TO WORRY ABOUT NON REPLICATED TABLES OPEN TRANSACTIONS ... This is my problem.IF no reports or smaller reports are running at taht replication works well without any problem. Because of this long running reports my log is getting full even in SIMPLE RECOVERY MODEL because my log transactions are not flushed yet.

As you said replication is common in DW application how they handle long running transactions at same time replicating some fact tables data

thanks in advance for your inputs.




Re: Log reader internal

Greg Y

Just out of curiosity, what makes you think these long running transactions are affecting the logreader agent Log reader agent only cares about committed transactions, if a trasnaction is not committed it will just skip it. Open transactions on non-replicated tables does not have any effect on log reader agent.

You'll probably need to do some more investigation as to what's blocking the logreader agent, if indeed there is any blocking. Have you monitored disk or CPU If this is SQL 2005, you can also try using tracer tokens to see the latency from end-to-end.





Re: Log reader internal

Gary Chen

One quick question. You mentioned that the report only queries the temp tables and all the temp tables are not a part of a publication, right Does the report query the actual fact table at all, or just purely the temp tables

Gary





Re: Log reader internal

SQLDBAs

You mentioned that the report only queries the temp tables and all the temp tables are not a part of a publication, right

YES. temp tables not part of publication.

Does the report query the actual fact table at all or just purely the temp tables

Report queries some published report parameter table (We call report definition tables. When user select different metrics we put the into these table we query aganist these tables . These are not bigger ones. Pretty small tables. However ,I replicate these tables to SERVER B. So, that It will same report definition , I can kick off more reports on this server also.) . I hope I am able to give little bit clear explanation on the situation