Commiting Transactions/Truncating Transactional Logs/Replication Latency
Feb 22, 2008
Hi,
I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.
1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.
2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.
3) What are the commands to truncate inactive transactions and increasing the readbatchsize?
4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.
Thank you so much in advance!
Sincerely,
Alla
View 3 Replies
ADVERTISEMENT
Jun 12, 2008
CREATE PROCEDURE [dbo].[proc_replication_latency_monitor]
AS
-- Procedure created by Henning Frettem (Lumbago), www.sqlteam.com, 12th of June 2008
BEGIN
SET NOCOUNT ON
DECLARE
@PublishedDBName varchar(200),
@SQL nvarchar(2000),
@PublicationPK int,
@PublicationName varchar(200),
@PublicationDatabase varchar(200),
@TracerTokenID int,
@Parmameters nvarchar(500)
DECLARE @Publications table (
PublicationPK int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
PublicationDatabase varchar(200),
PublicationName varchar(200),
TracerTokenID int,
TracerTokenPostTime datetime
)
DECLARE @Latency table (
PublicationPK int,
DistributorLatency bigint,
Subscriber sysname,
SubscriberDB sysname,
SubscriberLatency bigint,
OverallLatency bigint
)
--> Cursor for fetching all publications in all databases
DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT [name] FROM sys.databases WHERE is_published > 0
OPEN curPublishedDatabases
FETCH curPublishedDatabases INTO @PublishedDBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications'
INSERT INTO @Publications (PublicationDatabase, PublicationName)
EXEC sp_executesql @SQL
FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName
END
CLOSE curPublishedDatabases
DEALLOCATE curPublishedDatabases
--> Cursor for posting tracer tokens
DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR
SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications
OPEN curPublications
FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT';
SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT'
EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT
UPDATE @Publications SET
TracerTokenID = @TracerTokenID,
TracerTokenPostTime = GETDATE()
WHERE PublicationPK = @PublicationPK
FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
END
CLOSE curPublications
DEALLOCATE curPublications
--> Wait two minutes for all tokens to be commited at all subscribers
WAITFOR DELAY '000:02:00.000'
--> Then check the results for each posted token
DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR
SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications
OPEN curTokens
FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
WHILE @@FETCH_STATUS = 0
BEGIN
--> Insert token history for each token
SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
INSERT INTO @Latency
(DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency)
EXEC sp_executesql @SQL
--> Make sure that the PublicationPK is added to the token history
UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL
--> Clean up the tracer token
SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
EXEC sp_executesql @SQL
FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
END
CLOSE curTokens
DEALLOCATE curTokens
SELECT
b.PublicationDatabase,
b.PublicationName,
a.Subscriber,
a.SubscriberDB,
a.OverallLatency,
b.TracerTokenPostTime
FROM @Latency a
INNER JOIN @Publications b
ON a.PublicationPK = b.PublicationPK
END
--
Lumbago
View 7 Replies
View Related
Oct 19, 2007
Hello!
We have setup up transactional replication with dedicated distributor in SQL Server 2005 environment. I have noticed that during particular time of the day latency is increasing dramatically. I have been checking Tracer Tokens and Total latency during that time is around 30-40 min (both publisher to distributor and distributor to subscriber is taking much longer that normal). Normally, it is less than 10 sec. I was wondering if there is a way to pinpoint exact cause of the latency. This is pull subscription.
I would appreciate if someone can share (or point to the right direction) best practice on transactional replication setup/maintenance. My understanding is that only committed transactions are replicated, correct? I checked database on publisher and didn't see any outstanding long running transaction.
Any help is greatly appreciated.
Thanks,
Igor
View 3 Replies
View Related
Jul 31, 2007
I have been working on a project to evaluate transactional replication in SQL Server 2005 to find out if it will be suitable to provide real time reporting without impacting the primary database server. Thus far I have not seen latency better than several seconds replicating a change to a table or by utilizing the "tracer tokens" troubleshooting functionality. It usually takes a couple seconds from the publisher to the distributor and a couple more from the distributor to the subscriber.
I guess my question is actually whether it is possible to achieve sub-second (milliseconds) latency for transactional replication under any circumstances. Someone on another forum told me it was not, so I thought I would ask a separate community to confirm. Regarding the hardware, the latency on ping is less than 1ms between the servers and the servers themselves are decently powered quad processor boxes w/ 4GB of ram each. It seems to have the same latency of 3-5 seconds regardless of whether I publish a single table or all of the tables in the database. I currently have it configured with the publisher on one server and the distributor/subscriber on the second with the log reader and distributor agents set to run continuously. If it is possible, please recommend configuration of the agents or the topology of the PDS to achieve this. Thanks in advance for any insight you provide.
Dan
View 2 Replies
View Related
Jul 31, 2007
I have 2 servers; 1 in the head office and the 2nd at a remote location connected over a satellite. The satellite connection has typical latency and bandwidth. 2 mb down and 128kb up. Latency is average of 700ms.
Both servers have plenty of processing power and RAM and are very underutilized.
I have 1 push publication at the publisher at the remote location. The publication has 1 article. The table that is contained in the article only grows; ie inserts are the only statements applied, no updates or deletes. The table grows by 5 inserts every second(approximate). the table has 5 columns. none of the columns are blobs etc. An ID field, 1 datetime, 2 integers and 1 float column.
I have the agents set for continuous with a 2 second polling interval.
I am aware of the following command parameters for the agents but not sure which way to go to maximize the throughput of the data.
MaxCmdsInTran
SubscriptionStreams
ReadBatchSize
CommitBatchSize
PollingInterval
The subscriber table also has an insert after trigger to update another small table with the current value. Under normal network conditions, the trigger works great. Over the sat connection with high latency, the smaller target table seems to get a lock placed on it. I can only guess that the lock is from the inserts and perhaps a large number of inserts per transaction?
Under normal network conditions, the latency between publisher - distribution - subsciber is about 4 seconds.
What would be good command parameter settings for the agents to compensate for the latency?
Thanks,
Kevin
View 5 Replies
View Related
Apr 11, 2007
Recently we disabled the transactional replication on a prodution database(data size:300GB) , two weeks later we were aware of the transaction log of that db almost reached the limitation (70GB).I investigated the issues and found:
1. open transactions pending for distribution:
DBCC opentran('xxxx')
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (323830:7083:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2. exec sp_replcounters, got over 240,000 replicated_transactions, which caused the tran log extremely large.
The transactional replication has been disabled on this server, there are no publication, distribution DBs any longer, all replication-related agents are also gone, why did this still happen???
3. use sp_repldone manaully reset those 240,000 transactions to distributed, check opentran, it was fine for a while. The tran log file was freed up 99% after that.
4. check open transactions and run sp_replcounters today, found the over 60000 transactions again on the db.
Did anybody experience the same issue and knows how to fix this issue and stop this happening ?
Many Thanks,
Henry
View 4 Replies
View Related
Jun 7, 2001
I am having trouble Truncating a Transaction Log. I`ve tried everything in Book Online.
I`ve backed up the database, I`ve tried DBCC SHRINKFILE, DBCC SHRINKDATABASE, BACKUP LOG TRUNCATE_ONLY ...etc, but it will not shrink. Any suggestions ? Thanks.
View 1 Replies
View Related
Mar 1, 2007
Hi
I just wanted to know can you truncate transaction logs in SqlServer 2000 and if so how is this done?
Thanks
View 1 Replies
View Related
Dec 20, 2007
I've got a Sql server 2000 box that currently backs up to tape in full every night. I also want to back the box up off site but not in full (as 30GB is a little too much to transfer every evening).
So my plan was to do a full backup to tape at 7pm then a differential at 8pm (to transfer off site).
The problem I am having is that after my differential has been done the logs get truncated so if I want to replay them for any reason I need to get that differential back to site.
Anyone have any suggestions please?
View 7 Replies
View Related
Sep 20, 2007
How can you truncate database logs without disrupting log shipping configuration on that database
View 2 Replies
View Related
Jan 31, 2008
Hi,
I am a new DBA is small company. Some of the databases grows by few GB everyday, so I am thinking to set automatic truncating for those databases.
Is this a best practise?
Can you please recommend Ideal Recovery model after reading my current model:
1. I do Full backup every night.
2. After 6 hrs, Differential backup.
3. After 1 hr Log backup.
Also please suggest me how many time should I do Re-Indexing?
Thanks,Dipak
View 3 Replies
View Related
Mar 19, 2014
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?
View 4 Replies
View Related
Feb 12, 2007
hi
I have a transactional replication that was running fine for about few months, but eversince there was a massive update of records at the publisher site and when I use the replication monitor, i notice Status: Performace Critical, Subscription:[localservername]:[Orders_Repl], Performance:Critical, Latency:03:34:47.
I was thinking of redoing the whole replication but before i am about to proceed. Can you guys explained what happen and what does it meant when Latency:03:34:47. I don't remember seeing a Latency:03:34:47.
I am hoping to solve this critical performance issue hope you guys can help. Thanks
View 1 Replies
View Related
Jun 2, 2015
What is the measurement for Latency on the replication monitor? Is it hr:mm:ss, or is it mm:ss.ms?
I see
Performance = CriticalLatency = 2:41:52Last Synchronization = 6/2/2015 9:02:21 AM (current time)
Is the Latency 2 minutes 41 seconds behind or 2 hours 41 minutes behind?
View 4 Replies
View Related
May 25, 2004
Hi Everybody,
Can anybody tell me how to get the number of commands delivered per minute in case of Merge Replication with Publisher and subscribers.
This way, we can be sure that even if there is a latency (due to high volume transaction processing), replication is in good shape and things will catch up soon.
Also if there are any other similar measures which can be monitored to make sure that replication is going on fine, it would be great
Please let me know If anyone has got information on same.
View 1 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
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
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
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
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
Sep 11, 2007
I am using transactional replication between two SQL 2005 databases. On Sunday evenings, we run a maintenance plan that rebuilds the indexes on the publisher DB. When the plan runs, the replication receives errors and cannot continue. The error appears to be at the publisher level. The publisher runs continually and the subcriber runs every 10 minutes. The error states "cannot run sp_replcmds" Other than Sunday, everything runs fine.
Thank you
View 1 Replies
View Related
Jul 7, 2015
I want to set up the transactional replication. How many service accounts we need and what kind of permissions it required in windows level?Only two servers we are using. Pub/Distributor and Subsciber.Which option is better Push or pull?
View 1 Replies
View Related
Jul 27, 2007
at the 'specify articles" on the wizard. Some tables have a key next to it that doesn't let me select them to be replicated. This is happens on transactional replication. But if i do a snapshot, the keys next to the tables are not there.
How am I able to select those tables with a key?
=============================
http://www.sqlserverstudy.com
View 8 Replies
View Related
Jan 11, 2006
Hi All!
Need help guys... I'm new to SQL Replication, though I successfully created a transactional replication testing on my machine using PUBS DB and a separate DB.
I'm planning to establish/setup a Transactional Replication. There are two SQL Servers, one is being used by WebApp_1 and the other is being used by WebApp_2. Both servers are maintain by different administrators and situated on different domain/location (both used Win2003 Server). We will transfer some data from Server_1 to Server_2. Server_1 will be setup as Publisher and the other is the Subscriber, then additonalServer_3 will be added and will serve as the Distibutor.
My questions are:
1. Do I need to request separate domain account from the two different servers to establish connection to the Distributor?
2. Will the User databases from Publisher in compromise/affected as far as database settings and data are concerned?
3. Any useful links?
Any help provided will be greatly appreciated...
Thanks!
View 1 Replies
View Related
