Thursday, April 26, 2007 3:23 PM
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
Thursday, April 26, 2007 4:04 PMModerator
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.
Thursday, April 26, 2007 4:17 PM
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?
Monday, April 30, 2007 2:36 AMModerator
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?
Tuesday, May 01, 2007 6:10 PMModeratorTo 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.
Friday, May 18, 2007 1:11 PM
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.
Friday, May 18, 2007 1:24 PMModerator
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.
Friday, May 18, 2007 2:10 PM
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...
Friday, May 18, 2007 6:59 PMModeratorAs 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.
Friday, May 18, 2007 7:42 PM
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?
Friday, May 18, 2007 10:42 PMModerator
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.
Monday, May 21, 2007 8:07 PM
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.
Tuesday, May 22, 2007 2:25 AMModerator
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.
Tuesday, May 22, 2007 8:06 AM
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?
Tuesday, May 22, 2007 6:03 PM
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
Tuesday, May 22, 2007 7:32 PMModeratorSQLDBAs, as I mentioned in my last post, your report queries should have no effect on log reader agent. What makes you think log reader agent is blocked on your reports?
Friday, May 25, 2007 3:51 PM
Greg Chen ,
R you Logreader owner in MS? just kidding. any response to my question?
Friday, May 25, 2007 4:07 PMModeratorSQLDBAs, I am the test lead for Transactional Replication here at MS. Let me know which question we were supposed to answer, at the same time please read all my responses as well to make sure I didn't already answer it.
Tuesday, June 05, 2007 6:09 PM
While reports are running they keep open long transactions ( DBCC OPENTRAN) . Whenever LOGREADER hitting an OPENTRAN it will not move forward. FYI .. these TEMP REPORT tables NOT subscribed . ONLY FACT TABLE INFO GOT subscribed .
I guess there is a bug in transactional replication. you can reprdoce this one pretty easily
Wednesday, June 06, 2007 1:46 AMModerator
No, it is NOT a bug. This is by design. The log reader does not read past an open transaction and replicate anything further forward for a very good reason. It is required to keep every single transaction intact and in exact sequence. If the log reader just randomly skipped over open transactions in the log, it would create huge problems.
I'm getting really confused. You keep saying that these "temp" tables are not part of the publication, but you also say that you are replicating these report tables. If you have structures like this, why are you dumping huge volumes of temporary data into a permanent table within a database? Why not use actual temp tables and then you have zero conflict, because the transaction is in tempdb? You can also use functions and table variables to completely avoid this entire issue.
Thursday, June 07, 2007 11:22 AM
Here is the process..
WE GET DATA FEEDS FROM CLIENT .
LOAD THRU SSIS INTO FACT TABLE
CLIENT SUBMIT REPORTS(BASICALLY REPORT DEFINITIONS) THRU WEB .
BASED ON REPOPRT DEFINITIONS THE PROCESS CREATEs TMP_REPORT_ID Etc.. These TMP tables stored in my FACT TABLE DATABASE. THESE TABLE GRAB SUBSET OF DATA FROM FACT TABLE AND DO PROCESS .. WHILE PROCESSING THERE ARE LOT OF OPEN TRANSACTIONS.
per argument sake , IF Client submit 20 reports each may run for 10 to 20 hrs.( Yes) . at the same time if I get data feed from client I have to load them to FACT tables. & replicate the same FACT TABLE Data to another server. This is where I am getting problem
The replication is not moving if there are 10 big jobs runnign and they keep opening transactions for hours and hours. .. My log is getting full it is not truncating
I think there should be better approach for transactional replication .should not depend on NON - REPLICATION tables in the LOG space...
Thursday, June 07, 2007 3:06 PMModerator
Let's get one thing straight here. Reports <> inserting, updating, and deleting. If you are running a report, you are issuing select statements. Period. Now, part of the setup for your report might be to insert, update, or delete some data. But, you do NOT hold that transaction open for one millisecond longer than you need it open. That means you have a problem if your process looks something like the following:
1. Import data
2. Insert, update, delete some stuff from "temp" reporting tables
3. Do a bunch of select statements to dump a report out
If that is your processing, then you have a VERY big problem with the way it is constructed. It should look like the following:
1. Begin transaction
2. Import data
3. Commit transaction
4. Begin transaction
5. Insert, update, delete some stuff from "temp" reporting tables
6. Commit transaction
7. Run your report
If you are not explicitly beginning a transaction and then committing the transaction the instant you are done stuffing data into your "temp" tables, then your transaction is being held open for the entire duration of every select statement you execute to generate the report. That means you are holding transactions open for 10 - 20 hours at a time for absolutely no reason. This is not an issue for the replication engine. This is a design issue that is preventing replication from doing what it is designed to do.
Thursday, June 07, 2007 4:43 PM
We can't do like that feeds comes any time of the week, clients submits reoports any time of the day. they should run two independent tasks.. TMP tables having millions of recors the report process does updates at that time i guess transactions are opening.. I am just guessing if it is trans replication each records is marked for replication why don't it move those records instread of waiting for OPEN TRAN to complete
Friday, June 08, 2007 4:50 AMModerator
No one said that steps 3 and 4 need to execute immediately after each other. The point is that you are starting transactions and then leaving those transactions open for significant periods of time while you go off and do other things that are NOT part of the transaction.
Does your web page start a transaction, lock up data, and then wait for an end user to enter data or confirm something before it continues processing? Of course it doesn't. That would be locking up your data and leaving it at the whim if the user who might have left for the weekend or gone on vacation locking up your data until they come back.
You never, ever, leave a transaction open for a single millisecond longer than you need it. Take a very hard look at your code. If you code does not contain a single BEGIN TRANSACTION or COMMIT TRANSACTION statement, then that means you are leaving everything up to the creation of an implicit transaction. That means you will begin a transaction when you start doing something and commit the transaction when you disconnect. Everything between there is probably going to be in one massive transaction and it should not be.
The replication engine does NOT skip things. It does not skip things, because it is designed around one core principle that goes back over 15 years when transactional replication was first introduced to the product. (I know, because I was there deploying the very first system to ever use Sybase Rep Server and we deployed it with what I'll call version 0.1 side by side with one of the guys who was on the replication development team at Sybase. So, I was part of the team that deployed the very first SQL Server transactional replication system in the history of the product.) Transactional replication has always been designed around 1 core requirement: it will absolutely guarantee that every transaction will be applied to the subscriber in exactly the same sequence, without fail, that a transaction was committed. Transactions can have millions, tens of millions, or even billions or rows in a transaction log and anywhere within a transaction, you can be modifying a table that is participating in replication. In order to guarantee the single core principal that transactionl replication has always been designed around as well as balancing the need for the best performance possible under this requirement, the transaction replication has ALWAYS stopped reading forward in the log, the instant that it hits the first open transaction. This is no different than a transaction log backup. A transaction log backup stops writing the log out to the backup, the instant that it hits an open transaction. So, with both transaction log backups and transactional replication, the process terminates under 1 of 2 conditions: it either reaches the end of the log or it hits the first open transaction. Both processes do this in order to guarantee the integrity of the database, because violating that means that database integrity can be destroyed and you will never see a feature released that gives you the option to destroy a database.
The simple fact is that this is NOT a bug. This is absolutely the way it is designed and the way it has been designed for over 15 years and that design principle is NOT going to change. Your application design is such that it does not play well with replication and it is your application that is creating the serious latency issues. That means that it is up to you to figure out how to design your application around the way the replication engine is designed so that the two can play nicely with each other. You can go right ahead and submit the feature request to Microsoft to give the transactional replication engine a parameter that allows it to skip open transactions, but I can guarantee that I and tens of thousands of other customers who have production systems designed based on the fact that the engine never skips transactions or gets them out of order will be standing in line to oppose that request.
Your application isn't the first one that churns millions of rows of data within a database that is replicated. I have hundreds of these in production. None of them encounter the issues that you are describing, because we have designed all of those applications to ensure that no transaction is left open one millisecond longer than it needs to be and that every transaction is as small as we can possibly make it. This ensures that data moves from publisher to subscriber as quickly as it can possibly move and if we have things that conflict which are not replicated and lead to replication, we move them to another database. The transactional replication engine works on the transaction log and transaction logs can NOT span databases. So, if these "temp" tables are NOT replicated and they are causing processing conflicts with the replication engine, then you have exactly one option: get them out of the database where the rest of your data resides. You can't say that this can't be done. You can create 32,767 databases in a single instance of SQL Server and any one of those databases can issues queries against any other database within the instance as long as it has security rights to do so. You can even issue queries across instances as well as even across database platforms using linked servers. So, the bottom line is that these tables do NOT need to sit inside of the database they are in and getting them out of that database means that you now have no more processing conflicts with the replication engine.
Friday, June 08, 2007 12:22 PM
It is nice talking to roots of the Trans replication person. I am not saying it is a BUG it could skip the OPEN TRAN which is not relvent to my rpelication proces. I have suggested 2 options to my boss,
1. Schedule a time do not run any reports after the DATA load & replication completed we will start running reports.
2. As you said at the end move thes tables to another database and modify all SP with DATABaSE..TABLENAMEs.
Linked server is not an option I have tried with it . It is not real a quite good option because the app designed in such way every report metrics are dynamic. so, all reports queires are DYNAMIC query plans generate at run time only.
I have worked replication on OLTP application 100 seat call centers never had problem. This is the first time have seen since last 7 years, So, I relally know how TRANSreplication works behind the scens.
Thanks for taking time and enlighten me!