Transactional Replication And Deadlock + Suspect Agent Errors For Over 80 Databases
Oct 25, 2005
Hi,
I have more than 80 databases on my publisher (SQL Server 2000 SP4), I tried to enable Transaction Replication on all of those databases instantly through some T-SQL programming and DTS Packages. Every thing works fine until the snapshot agents starts to take sanpshot from the publisher databases. As soon as their snapshot agents start for those 80 databases, they start giving the deadlock error. All 80 snapshot agents starts at the same time.
Error Message:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Error Detail:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
(Source: Server_Distribution (Data source); Error number: 1205)
---------------------------------------------------------------------------------------------------------------
WHY???
View 2 Replies
ADVERTISEMENT
Feb 7, 2000
I setup Transactional replication between two SQL Servers.
I am getting the following errors: " Could not find stored procedure 'sp_MSins_stores'." and the job fails.
I dont have any idea why I am getting these errors. I am getting this error for every table in the publication.
Any suggestions are welcome.
Thanks in advance.
View 1 Replies
View Related
Aug 2, 2001
Unable to start log reader agent after being manually stopped.
Any ideas.
View 1 Replies
View Related
Jun 27, 2007
Why would you schedule a Snapshot Agent to run daily, weekly, monthly, etc. for Transactional replication when the transactions are replicated once the change is committed to the Publication database?
Does this help to replicate DDL against the Subscriber?
Thanks,
Thomas LeBlanc
View 1 Replies
View Related
Sep 7, 2006
When you create a transactional Replication.Is there a way to disable to snapshot agent scheduler. I believe that there is a way to disble it when you create a publisher.
Help ME!!!!!!!!!!!!!!!
View 3 Replies
View Related
Oct 13, 2006
I am investigating the feasibility of a configuration with 3 databases on SQL2005
DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions
DB_B is a subscriber database which subscribes to pub_txn
DB_B is also a publisher which serves up merge publication pub_merge
DB_C is a subscriber database which pulls pub_merge
===============================
Updates on DB_A are successfully replicated to DB_B
Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B
===============================
Updates on DB_B are successfully replicated to both DB_A and DB_C
===============================
Updates on DB_C initially failed with the error
Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.
I then changed the login repllinkproxy to be a db_owner in DB_C
I now get the error
Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.
=================================
I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?
2) Why is update on DB_A not reaching DB_C
3) Why can't I update DB_C?
Any suggestions gratefully received
aero1
View 10 Replies
View Related
Mar 19, 2015
Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?
The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.
We have a post snapshot script that updates these additional columns on the subscriber.
I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.
Is this possible?
View 1 Replies
View Related
Jun 14, 2015
I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
View 3 Replies
View Related
Nov 3, 2006
Hi all,
I have implemented Peer-to-Peer Transactional Replication only for two servers.Everything works fine but in highlevel of concurrency some updates are rolled back due to deadelocks(Never comes without replication).Is there any type of document which can guide me to avoid deadlocks (under Peer-to-Peer Transactional Replication env).
View 9 Replies
View Related
Dec 1, 2006
Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
View 4 Replies
View Related
Aug 29, 2006
I'm setting up a push merge replication, after the initial merge agent completes without errors I move the subscriber from a wired connection to a wireless connection. At this point I get an error€¦
The schema script €¦Program FilesMicrosoft SQL ServerMSSQLREPLDATAunc[table]_93.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Cannot drop the table '[table]' because it is being used for replication.
(Source: dfdmfvltgh09 (Data source); Error number: 3724)
After this error the initial merge agent kicks off again trying to apply the initial snapshot where I then get the following deadlock errors.
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
(Source: dfdmfvltgh09 (Data source); Error number: 1205)
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
All are getting set up on sql 2000 sp4 on a push. We are trying to set up 10 units that all are pointing to the same dlink router model dwl-2100AP. 5 are working the other 5 are coming up with this error. Anybody have any ideas what could be causing this. I don€™t mind the getting cut off due to poor network connectivity, I really want to stop the initial merge agent from kicking off after it€™s already completed.
Thank you in advanced,
Pauly C
View 3 Replies
View Related
Sep 30, 1999
Hello,
Today we found a suspect database and after consulting this website went and ran sp_resetstatus on one database with success. After which we stopped and started the server and ran dbcc newalloc, dbcc textall, dbcc checkdb on that database. It successfully changed from its suspect status and random queries showed that the data was still intact.
Exactly the same process was run on another database and it failed to move from the suspect status after restarting the server. We then went through books online, which advised to update sysdatabases setting the database's status to 0. This also failed.
HELP PLEASE.
Does anyone know what else to try? This was a development database with no backups. This would save a weeks work. Thanks for you time.
Cheers
Damian
View 1 Replies
View Related
Nov 26, 2007
I have 2 sql2000 servers that i have log shipping setup on
On weekends the optimization plan runs creating some large log files that take several hours to restore
In the process most of the databases at the secondary server are marked suspect
Usually I restore the last backup manually and log shipping catches up, but this takes most of the week to do
Why are the databases mark as suspect.
And is there an easier way of fixing the suspect databases, I not sure if i should just run sp_resetstatus
View 8 Replies
View Related
Apr 20, 2007
Hi,
I have two servers, one production server and one backup server which have transactional replication with a pull subscription.
When I configure replication, it works fine during our test weekends testing production load. After tests, replication looks fine for a random number of days. Then, all of a sudden, an error message is displayed on one of the agents: "The agent is suspect, no response within 10 minutes." This has happened a number o times. If I remove replication and configures it again, it always works. Sometimes it works by just updating one of the tables and the error message disappears. The last time (today) that did not work. Updating the database did not replicate and the error message remained.
Has anyone experienced this same problem and has a god solution. One thing that is common is that the error message appears after long times of inactivity on the servers, or perhaps after a restart but that I am not sure about.
Question 1: How can I prevent this error message?
Question 2: Are there any special things to think about when I need to restart the servers and replication is configured, e.g. after installing updates from Windows Update.
I would be very grateful for any answers regarding this.
Best,
/M
View 1 Replies
View Related
Sep 26, 2015
After cloning our production SQL 2012 server to make DEV environment, there is a bunch of databases in "Suspect" mode. This is because the DB's and Logs have got out of sync.I can clean up each DB with this script:
ALTER DATABASE DB_NAME SET EMERGENCY
GO
DBCC CHECKDB (DB_NAME)
GO
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE DB_NAME SET MULTI_USER
GO
but this takes a long time replacing DB_NAME and executing for each DB.I tried putting it all into a script with variables, but I have done something wrong and it does not work. Also is there a way to just apply it to the suspect DB's?
USE master
Go
DECLARE @dbname sysname, @cmd varchar(1000), @cmd1 varchar(1000), @cmd2 varchar(1000), @cmd3 varchar(1000), @cmd4 varchar(1000)
DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases
OPEN db_recovery_cursor
[code]....
View 7 Replies
View Related
Sep 13, 2007
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies
View Related
Jan 20, 2004
All of a sudden I have 4 of my databases, including pubs that are all marked suspect and seem to be un-recoverable. I have followed the Resetting the suspect status directions as well as trying to attach only the .mdf files but still run into problems reading the .ldf file. I have not tried to do the procedure of creating another database with the same name and structure then swapping the files to trick SQl but will do so if I need to. Does anyone know why this happens all of a sudden to multile databases, of which Pubs I have never used for anything??
In my log files during startup I get this error
Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
Then this error for every database that is suspect, (the actual LSN numbers are different for each one).
The LSN (4:517:1) passed to log scan in database 'pubs' is invalid..
Is there anything else I can do to prevent this from happening or recover the DB's
Thanks
View 2 Replies
View Related
Jan 12, 2007
We have quite a lot of Transactional replication, and over the past few weeks it had become quite slow. I spent the last few days trying to resolve the problem and found that a Push subscription was delivering transactions at about 50/sec and a Pull subscription was delivering at around 700/sec.
This test was run on the Same publication, same subscriber so hardware and links are all the same, the only difference was the type of subscription. I've since changed a few of our subscriptions for the larger publications and have found they run quicker than previously.
Has anyone else seen this behavour and is there a valid reason why this would occur. The distributor and subscriber are both running SQL2005 SP1 and the publisher is SQL2000 SP4.
If anyone could shed some light on this it would be appreciated.
View 7 Replies
View Related
Sep 2, 2015
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. Â I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. Â I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. Â SQL-P (publisher),Â
SQL-D (distributor) and SQL-S (subscriber). Â Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
View 6 Replies
View Related
Jun 28, 2001
Hi all,
I have a db (with replication enabled) which was marked as suspect. As per the logs it is the Hardware crash that caused this db to become suspect.
Tried with sp_resetstatus to recover the db. But it is still in suspect mode. Changed the db into emercengy mode to recover the data thru' bcp which was succesful. Since there is no other way to recover the db, now I want to drop the db and create / restore the db from backup.
I am unable to drop the db becasue it is having a publication. I am also unable to drop publication becasue now the db is in emergency mode.
Kindly help and inform how I can drop the db and create it again from the backup without reinstalling SQL Server again.
TIA.
Ravi
View 1 Replies
View Related
Feb 19, 2007
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
View 4 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Apr 29, 2008
Hi
I want to suspect database
stop server first
I try to rename C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDatamsdbdata.mdf to msdbdata.sav
and then start the server
use command to check:
SELECT status & 256 FROM master.dbo.sysdatabases WHERE name = database_name
if the result is 256,it means the msdb is suspect,but the result is 0,it same as the normal status
do you know how to set database suspect with this way, or do you know other way to suspect databse.
absolutely,I could re-back my server noraml with your way
Thanks
View 6 Replies
View Related
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
Jul 30, 2001
On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.
View 1 Replies
View Related
Dec 28, 2000
Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.
Thanks!
Kavira
View 1 Replies
View Related
Feb 13, 2001
I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me
Thanks
View 1 Replies
View Related
Nov 1, 1999
I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -
The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'.
Access is denied.
The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the (
epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.
What am I missing?
View 2 Replies
View Related
Oct 25, 1999
Hello,
I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...
Thanks
View 1 Replies
View Related
May 22, 2003
Hi guys can anyone help me with this issue. if so thankyou kindly.
1. created transactional publication (for tables, stored procs & views )
2. created empty database at subscriber
3. set up pull subscription at subscriber
4. ran the initial snapshot agent at the publisher
5. checked the schema files & found out it hadn't scripted the primary keys on the tables.
Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.
? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?
? Should I break my publications into 3; say 1 for each object?
Thank you guys for taking the time to read this.
View 1 Replies
View Related
Feb 24, 2005
Hi
I am tring to implement transactional replication.
How do i publish tables which donot have primary keys?...because transactional replication seems to publish only the tables which has a primary key.
Thanks
Madhukar
View 7 Replies
View Related
May 1, 2002
Hello all! I am setting up my first Replicated database, and want to get this right.
We are a Newspaper, and I will be Publishing the database that is the back end of the web server. The goal is to load balance the web site as well as to have a second identical server in case something goes wrong. Both the Subscriber and the Publisher need to be kept in sync as new articles are posted, so my client will just be updating the Publisher. But I'd like to keep a single set of usage statistics, so I was thinking of using Immediate Updating so the Subscriber can update the Publisher using two-phase commit.
What will happen if the Publisher shuts down and an update is attempted on the Subscriber? Will it just hang? Let's assume the worst - what if my Publisher is kaput? How do I determine that my other database is a Subscriber; and how do I stop it from being a Subscriber?
Thanks in Advance,
View 1 Replies
View Related
Jun 28, 2002
Hi
I have a requirement for pushing transactional publication to various subscribers. Each subscriber will need to receive a different subset of rows from a table. So I need all rows published, but with horizontal filtering at subscriber level. Is there a way of applying filtering at the subscription level.
Thanks
Tariq
View 1 Replies
View Related
