I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.
I need to configure a replication which will gather logs from the publication once in a day.
I have a publication on Sql Server 2012 that uses transactional replication to 7 subscribers (these are a mix of Sql Server 2008R2 and Sql Server 2012). Last night I scheduled the Snapshot job to run to "re-publish" the database to the subscribers. I had a few new table to push down. Unfortunately the snapshot job became the deadlock victim. Now updates to the publisher are not being sent to the subscribers.
Short of rerunning the snapshot job, is there a way to repair the replication so the updates to the publisher are pushed to the subscribers? The "re-publish" can only be run overnight when there is very little impact to users.
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Getting below error most of the times .
"Server was unable to process request. ---> The transaction log for database 'xxxx' is full due to 'REPLICATION'."
I am doing below steps to clear the log everytime.
1.EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1
2.checkpoint
SQL 2012 Ent SP2
Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.
I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.
The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.
I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).
I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.
I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.
Do I have to remove replication completely to get round this? Surely not.
I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?
I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.
I have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?
If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).
Setting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.
But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?
What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?
Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.
Good Morning,
To perform backup of the transaction log every 15 minutes I made several schedules of 15 in 15 minutes. There were about 96 schedules. After those schedules SQL Server 2005 has been very slow. There is a way to create only 1 schedule and make that creates files every 15 minutes?
How I could do the schedules of the transaction log?
Thank You,
Ralph Haddad
I want to list out the pending transaction for transaction replication by publication.
Help needed.
I've set up Transactional replication Pushed from SQL 7 to SQL 2000. All works fine accept I have noticed that the snapshot agent is running once a week and storing a lot of data I don't really need in c:msql7
epldataunc
eplicationnamedatastamp. This is gradually building up and as C is a smallish partition it'll only survive for another week.
My questions are:
1) Can the Snapshot schedule be disabled? I'm under the impression that it's only required when a new subscriber comes along which is never! If I do need a new subscriber I could just manually produce another snapshot.
2) Can I delete the previous snapshot folders under ReplData? On a development server I renamed one of the folders and the ground didn't open up below my feet.
3) What effect would changing the working folder used by the distribution agen have, the distributer is on the publisher (which as I mentioned is pushing) I've tried changing the working folder, on a development server, I manually ran a snapshot which whose BCP files all ended up in my new location and the Transactional Replication didn't seem to care.
I've looked in a few places for a difinitive answer and I'm not having much luck so any help (preferably before saturday) would be greatly appreciated.
Regards.
Hi,
Is there any method that I can use to backup the whole database to another database without using replication? I have to backup it every half year. Can I do something like setting schedule to run to "Database Backup" module in sql server?
Thanks,
Matt
We have many customers with a large database, many workers and many database transactions. Every night the databases are "full backupped". Regularly the logfiles (dbname_log.ldf) grow very large, for example a database (dbname.mdf) of 60GB and a logfile equal in size. When the drive becomes too full, I truncate these logfiles to free diskspace.
Question: is it a good decision to run a script that, after the daily full backup, truncates the logfile if it is larger than for example 10GB?
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedI have Snapshot set up as a schedule task between server A and server B.
If I make changes to the db schema on server A - the changes are not replicated to server B.
Is this normal?
Do I have to restart the Sanapshot agent each time there is a schema change?
Thanks,
Bianca
I need to schedule daily backup at 6:00am. how to do this. I'm on Sql Server 2008r2
View 1 Replies View RelatedNormally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.
View 2 Replies View RelatedDear all,
I set up a transaction replication (from server A to server B) for tables and stored procedures. When I update the data at server A, the data at server B will be updated by replication. However, the stored procedure can't update at server B no matter what I changed at server A's stored procedure. How can I update the stored procedure using transaction replication?
The other question is the table schema. As I know, the transaction replication will be created a snapshot to project all needed tables and stored procedures at the first time. If I created a new table, stored procedure or changed the table schema (e.g. add/delete a new column), the transaction replication didn't update the new objects to the server B. What can I do to update the transaction replication except creating a new subscription?
Thanks.
epjames
Good Morning to all,
I am new to SQL Server.Here is my question. I have set up transactional replication with immediately updating subscriber between to servers A & B. A is publisher/Distributor and B is subscriber. In the event A fails the applications will be directed towards B. My problem is suppose A has faliled and my data is now written to B. How can i syncronise data in A and b when A is again online and what should i do ?
thanks in advance.
Minesh.
Hi all,
I am using SQL 2k on two seperate servers on win 2k advance servers without any service packs. My problem is i am using transactional replication between two servers (SML1 and SML2), SML1 is publisher/Distributor and SML2 is subscriber. My db name on both the server is same. when i am inserting data in db on SML1 it replicates immediately on SML2 but vice versa in not happening. Any help in setting up two way replication is highly appreciated. (There is no timestamp column on table which is used as article and there is PK).
thanks in advance.
Minesh.
I keep getting this error message even though I have droped and recreated
the replication several times. Has anybody had the same problem?
error message.
"Another snapshot agent for the publication is running"
Thanks for your time.
My Transaction Replication fail with this error message pls help.
This is the error message The process could not create file 'SQLMS1d$MSSQL7ReplDatauncSQLMS1_AFT_National_EMN _National - Transaction - Changes at Both Sides - EMN to Local Only20000607090006Benefit.sch'.
I it possible to setup transaction replication between two sql servers if both Sqlerveragents runs under different accounts and the servers are in different domains
Thanks in advance
Langel
I am attempting to set up transactional replication between a production server (SQL1) and a “reports” server (SQL2). The production server is the publisher, the “reports” server is the distributor and the first subscriber, other subscribers will be added later. There are multiple databases on the production server; selected tables from each will be replicated to corresponding databases on the other servers. Both are SQL7, no SP (yet).
I have set up the replication on two databases so far. The initial snapshot replication runs fine; the tables and data are duplicated on the reports server.
For one DB the transaction replication fail the other still reports “No replicated transactions are available”, which is probably true.
The failing log reader agent history reports: “The process could not execute ‘sp_replcmds’ on SQL1“ (five time, 10 minutes apart). The session details shows 2 items, “Initializing” and the same error message. The error details show it as an ODBC error timeout.
After the fifth time the log agent history reports “The process could not execute ‘sp_Msadd_repl_commands27hp’ on SQL2 ” The session details show 3 items , “Initializing”, “No replicated transactions are available” and the same error message. The error details show the following (Category, Source, Number, Message): Agent, blank, 2000, “IDistPut Interface has been shut down.”; Agent, SQL1-JCL1, 20007,” No replicated transactions are available.” ( 26 times); Data Source, SQL2, 1007 “Cannot insert duplicate key row in object 'MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'MSrepl_transactions' with unique index 'ucMSrepl_transactions'.”
I can restart the agent, but after two hours the same thing appears. These were set up with EM using the wizzards
Any suggestions would be greatly appreciated.
I had established a transaction log replication ,it's not immediate-updated.
on next 2 days,it's normal.but now,it's not available ,and haven't any warning,error,informatin.
I have very trouble about the 'log reader' is running but i change the data in articles it's no effect.
please tell how i do?
Hi,
Every one
Can I add coulmn in my existing table which i am using for replication. This article is i am using for transaction replication. I added column to both articles , publisher & subscriber, But now it is giving me error that, Insert error.
I checked in Create & Manage publications property on E.M , that cloumn is not checked & it is not allowing me to do from E.M , Does any one know
which store proc I require to execute from QA.
Appreciate Your Help
Thanks
Nirmal.
I am trying to setup real time transaction replication between two databases on two different sql server 7.0.The ideas is to replicate all the objects from the first database to the second database including table, stored procedures and views. Both SQL servers’ runs under different service NT account.
The sql server that I want to replicate runs an application that create additional tables when the application is running.
How do I schedule a transaction replication so that all the objects plus those additional tables and stored procedures that the application creates at run time are also going to get replicated in real time?
Thanks in advance.
JACK
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
View 4 Replies View RelatedHi All,
I have been asked to take over the maintenance on dB servers that I have not originally 'set up'. The request came as a result of the database log files growing at such a rapid pace its taking up hard drive space. I ran sp_dboption and noticed that the log files are NOT set up to truncate on checkpoint. As in SQL 2000 I wanted to determine if the dB was set up in 'simple' or 'full' recovery mode. It appears they are set up in 'full' recovery mode (which we can modify, since this is not a transactional dB and we rarely if ever have to restore from the transaction log). Hence my question,
Is there anything I need to worry about (regarding replication)if I change the dboption to truncate on checkpoint and set dB in simple recovery mode?
And after I backup the transaction log and try to shrink it will that have any adverse affects? I feel like this is all I need to do but replication is new ground for me, any information regarding it would be greatly appreciated. Also, any advice on how to maintain the transaction logs would be helpful.
Christine
How terrible my life! save me!
Do you know the function of system store procedures for replication in publish database and distrubtion database? please tell me.
I'd established 'transaction log' replication, it's like something is full or can't active,i don't know what process is unvalid.
How i do!
Hi,
Every one
I have added new column in my Publication - Article Table ,
Same column I have added in my Subscriber Table ,
I am doing Transaction replication , but now its giving me error
Insert Error - Column Name or Supplied Value doesn't Match,
If any one has any idea please suggest me
Thanks
Nirmal
Hi all,
I am using SQL 2k on two seperate servers on win 2k advance servers without any service packs. My problem is i am using transactional replication between two servers (SML1 and SML2), SML1 is publisher/Distributor and SML2 is subscriber. My db name on both the server is same. when i am inserting data in db on SML1 it replicates immediately on SML2 but vice versa in not happening. Any help in setting up two way replication is highly appreciated. (There is no timestamp column on table which is used as article and there is PK).
thanks in advance.
Minesh.
Besides loopback detection, is there anything else that is needed to prevent the replication command from sending back to the source. Cos i am having problem with the subscriber B sending back the replication to A in the form of B as the publisher and A as the subscriber.
:confused:
Two Servers,1st 2nddatabase a -------> after replication a'b' <------- after replication bon 1st server a is replicated to 2ndon 2nd server b is replicated to 1stI tried with merge replication it is working fine.But if i tried to usetransactional replication it give some error like "accessviolation".Using tansactional replication is it possible.plz help me toslove this problem.Regards,Senthil prabu R
View 1 Replies View RelatedHi all, using transactional replication in SQL 05, I have a schema that is different at the subscriber and wanted to know the best approach for transforming the published data. For example, the subscriber is more normalized than the data being published causing 1 published record broken out into to many tables at the subscriber. Also need to applying scalar funtions to a few published columns before committing at subscriber.
I've read DTS, although backwards compatible, is being deprecated in a future release. I suppose I can use either SSIS or custom procedures. I didn't see any tie-in to SSIS in the system procedures to setup replication (only DTS). Any advice on which would work in this type of scenario?
Much appreciated, Pat