Hello,
I have this problem on a Production database.
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
Thank you.
I've already shrunk the tlog from 350 GB to 313.My DB Server (2008 R2 Sp2) cannot be restarted and the db cannot go offline or detach due to company policy.My DB after changing from full to simple mode still has 313GB tlog file and when I run DBCC OPENTRAN I get Transaction information for database 'DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2882:26:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which means this DB is participating in a High Availability process like replication, mirroring or log shipping.
So I run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1.
This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.
But I get an error:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
There are currently 9 connections and all are sleeping.What else can i try in order to shrink the tlog file?
I keep seeing this return from running a DBCC OpenTran:Transaction information for database 'Live_App'.Oldest active transaction:SPID (server process ID) : 92UID (user ID) : 1Name : DTCXactLSN : (12837:1924:1)Start time : Oct 4 2004 8:54:03:570AMDBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.I don't see anywhere in code that begins a transaction with the nameDTCXact explicitly. Is this a generic name for any transaction that isopened without an explicit name? The problem I am having with this isthat sometime it will start and may not get commited or rolledback forquite some time. I have seen it remain for over 1 1/2 hours before.Would that be caused by the application not cleaning it up?Your help in explaining the source of this will be appreciated. I didfind an entry on Microsoft.com that used the word DTCXact. It wastalking about Transaction Propagation from Resource Manager ToApplication. I'm not sure if this applies to what I am seeing here ornot.Thank you.Kalvin*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.
Thanks in advance
Regards
R Suresh
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 RelatedHi,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 RelatedHi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies View RelatedWhat is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedHi,
I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows
- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem
- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine
however.......
- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error
- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.
I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.
I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc
Any advice appreciated !
Thanks
Ronan
Hi all,
I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????
Thanks in advance
Jacx
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
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.
Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies View RelatedI 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...
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
View 2 Replies View RelatedI have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.
I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?
what conditions a job step my be showing in the job history as both running AND completed successfully?
I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
Everyone known how to fix this problem?
Thanks,
Frank
Hi,
I have a particularly difficult problem.
I have a SQL Server in an internal network and need to "replicate" to an identical server and database in a DMZ. The DMZ can only receive files sent by a custom component and no port(s) can be opened in the firewall or standard FTP used.
I also need to minimise traffic (i.e. sending whole tables is not an issue as some contain millions of rows), replicate approx once hourly and allow record locking only (as opposed to DB locking/exclsuive access or table locking).
Any thoughts / experiences greatly appreciated otherwise I'm looking at putting triggers on all the tables to monitor changes and generate SQL statements for execution against the DMZ server.
I want to test SQL Server replication. Can I create two databases and replicate between on the same server as to give me an idea on how replication will work?Or should I use two completely seperate SQL servers to test replication?Any good links to setting up the replication?Thanks!
View 2 Replies View RelatedI am following the wizard to setup SQL Replication. My understanding is that I can create the Publisher and Subscriber on the same SQL database server. I have setup two databases TestA and TestB and TestC being the publication database, on the same server DatabaseA. The first warning I receive is that SQL Server Agent on Database A currently uses the system account which causes replication between the servers to fail. In the following dialog box, specify another account. I have set up another user account for DatabaseA with full writes to everything and specified the user account in the dialog the warning message spawned. I have put the SQL Agent user in all three database I followed the rest of the wizard and on the Specify Articles to Publish, Tables appear as the object type, Show is checked and Publish Article is unchecked. I try to check Publish Article for the tables object, and it is locked and I cannot go any farther in the wizard.What am I doing wrong? Do I have to use two different servers to do replication?Please advise!Thanks!DotNetNow
View 1 Replies View RelatedI have been ask to set-up a replication & I wanted to know what factors do I need to consider before a replication can be set-up?
Regards,
I have Replication working on 2 servers running SQL Server 7.0 with SP3. I am replicating select tables from 3 databases using Transactional Replication. I setup P1 as the publisher and the home for the distribution database and P2 is the Subscriber. I setup a push subscription to P2. It is working fine. Just one issue. On P2, I don't have Replication Monitor and I'm not sure why. We just installed SP3 on these two servers. In order to do this on a Clustered environment, we were forced to drop replication and then re-add it after installed the SP. After all this was done, I re-added replication and now P2 does not display Replication Monitor.
Any Ideas ?
Thanks,
JoeT
Hi,
Has anyone done replication from SQL Server 70/NT to DB2 before?
Anyone has any tips or guidelines which may help me in going for it - like rights/permissions/certain Stored procedures/certain service packs etc...
Any help appreciated.
pvd
Hi friends,
Our MS SQL Server Tables(2 Nos) needs to updated in the AS/400 Server every day. How do i proceed with? Is there any tool is avaliable? or MS SQL Server / Windows NT will have the provision to communicate and update in AS/400 Server.
Thanks in Advance for reply.
Murali
Dear Listers,
I need to replicate a certain table from the SQL Server Data Base on NT to the AS/400 (as subscriber). It is only one way, and I know that some product by starsql will probably work.
I do not know the details and am a little foggy about them. Can someone point me to an source of information on the sharing of data between NT and AS/400?
Thanks,
George Frothingham
We are trying to setup transactional replication across the Internet where the publisher and the subscriber are on different domains. Is this possible?
Thanks.
We want to implement bi-directional replication with sql server 6.5.
Do you know companies that have software products that can implement
bidirectional replication for sql server 6.5?
We have one central server and 5 region servers.
I believe someone in this forum replicate using tcp/ip. I am trying to do one for the past one week, facing an error message " not able to establish a trusted connection". I read the books online, Microsoft tech help,none give me a solution to this.. Can someone help me out.
Thanks a lot in advance
ranga
I hope someone can shed some light on a problem I'm having with SQL replication. I need to setup replication to a server on the other side of a firewall. Both boxes are SQL 6.5 and are using Standard security and multi-protocol. The publisher/distributor is a member of a domain and the subscriber belongs to a workgroup. Problem: How can I replicate between these servers without opening every port above 1024 for RPC as Microsoft suggested?
View 2 Replies View RelatedI have been trying to use SQL Server replication for synchronizing offline slices of data with our main corporate database.
I have been playing with replication for the last couple of weeks, but have a few problems.
I have a merge publication in the corp database and have pull subscriptions in each of the slices. I use the SQLMerge object. One slice always works but when I tried another slice on another box, it kept complaining "subscription is invalid" and "call {sp_MSgetreplicainfo(?,?,?,?,?,?)}" error. it seems like the values for the subsriber, subscription_db etc are not being passed to this stored procedure.
If you have any suggestions or good references on Replication, could you please let me know.
Thanks,
Hi everybody
I would like to ask a question, maybe stupid. I've got a sql server database and i would like to implement replication. First of all i would like to know if there's any special requirements that my database must meet before implementing replication. All of my tables got a column named "ID" wich is an int 4, identity. Can i implement replication without making any changes to my database ?
Hi,
I am planning on setting up disaster recovery configuration. It is planned to use transactional replication from the production server to the DR server. Are there any recommendations for replicating changes back to the production server, once it is back online. I suppose that it would be safest to move the database back the other server over a weekend or during downtime. But I am just wondering whether anyone has experience of other practical solutions.
Many thanks
David