SQL Server 2000: Replication Latency Health Check

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


ADVERTISEMENT

How To See Health Check In Sql Server

Apr 9, 2008

Hi,

can anybody help me that how can we see sql server Health check with t-sql.Because i am trying to make script for this
Basically i need these info..
1.Current Date
2.Current time
3.Session,id
4.Session name
5.all locks
6.cpu utilization
7.how much disk space is used inside the database
8.how much space is free inside the database
9.Date read
10 Date write
11.how can we see process information with t-sql or with stored procedure

and i have solved 6 problems from this list i made queries for this .
will u plz help me
thaxxx

jagpal singh

View 2 Replies View Related

Database Server Health Check

May 9, 2007

I want to run Profiler Traces, Perfmon Checks and whatever else to see what hits the server the hardest in order to work out where I can make performance improvements. We don't have any massive problems other than more timeouts than I would expect from web users no-and-then when they do pretty simple free-text searches. I just want to see if I can lighten the load on the server in general.

Where does a DBA get the most ROI? I don't want to drift into the realm of diminishing returns where I am making changes that have no noticeable impact.

What should I start measuring first and how do you measure it? (E.g. if I should monitor disk writes in Profiler, how many writes are too high for a statement? 100? If so, is one 100-write statement per hour OK but one 100-write statement per second is not OK?)

If there is already an article for this, my apologies. I'd be grateful if you can point me to it.
Is http://www.sql-server-performance.com/ a sufficient one-stop-shop for this question? If so, have you found some articles there to be more useful than others?

View 5 Replies View Related

Microsoft Cluster Server Health Check.

Jun 29, 2005

Hello,

View 2 Replies View Related

SQL 2012 :: Anyway To Check If Server Is Having Disk Latency

Oct 1, 2014

Is there anyway to check if server is having disk latency or IO issues?Found below in SQL error log

Date10/1/2014 8:28:58 AM
LogSQL Server (Current - 10/1/2014 12:00:00 AM)

Sourcespid10s

Message
SQL Server has encountered 8500 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:Fin.mdf] in database [Fin] (5). The OS file handle is 0x0000000000001368. The offset of the latest long I/O is: 0x0001104a7da000

View 1 Replies View Related

Replication Latency

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

Monitor Transactional Replication Latency

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

Troubleshooting Transactional Replication Latency

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

Typical Latency In Transactional Replication

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

Replication :: Measurement For Latency On The Monitor

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

Heeeeellllllllppppppppp!!!! Replication Latency In Dist Agents

Dec 14, 2005

Hi,

View 6 Replies View Related

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 View Related

Transactional Replication Over Satellite Connection. Set Parameters For High Latency?

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

SQL Server Health And History

Oct 5, 2004

Anyone had time to play with this yet? Wanna know if it was worth playing around with.

View 2 Replies View Related

Microsoft SQL Server Health And History Tool Version 2

Sep 15, 2004

Recently I've installed Microsoft SQL Server Health and History Tool Version 2

and The SQLH2 Reports are a set of reports that provide different views on the data

that the tool collects. But unfortunately I can be able to see Data on Performone counters

Report. I can not find optional component SQLH2 Performance Collector is a stand-

alone service of Microsoft Windows that collects and stores performance counter data from

selected servers.

The Deployment guide tells :

Performance Collector is optional, and is available for download from the Microsoft Download Center (search for SQL Server with keyword SQLH2): http://www.microsoft.com/downloads/search.aspx


But it does not give result

Where can I find the missing component ?

Thanks

Oleg


:(

View 1 Replies View Related

T-SQL Code To Check The Status Of Sql Job On SQL Server 2000

Jan 7, 2008

How to query the database to see if a job is still running?

View 1 Replies View Related

SQL Server 2014 :: Does Running Multiple Queries In Same SP Cause Latency

May 14, 2014

I have the following:

(a) One Dynamic SQL Query that takes 37 ms when run as a single query or in an SP.

(b) Three SQL Indexed View queries that take 0 ms when run together.

When i add (a) + (b) in the same SP, i should get 37 ms + 0 ms = 37ms, but NO it takes 400 ms.

What is causing the extra 363 ms of latency.

View 9 Replies View Related

Sql Server Doesn't Work Well With 250ms+ Network Latency

Oct 15, 2007

Hello,I'm an absolute newbie when it comes to SQL. I was told that SQLserver does not function well on a WAN where network latency between,say, the SQL server and a front-end server is greater than 250ms.I can't find anything information supporting this claim online, so Iwas hoping someone here could tell me if this is true or not?Thank You!!

View 2 Replies View Related

What Is The Best Way To Achieve Best Latency For Reads And Writes To SQL Server 2005?

Oct 18, 2007

I am looking into various options to improve latency of our application (we figured the latency is mainly because data persistence - writes and reads from DB). I am looking into In-Memory databases also. But, before making that decision (of using in memory databases), I would like to see if there is a way to configure SQL Server 2005 to get as close performance as in-memory databases?

My question:
1. Is there a way that I can configure SQL Server 2005 to use a CACHE that gets loaded as needed basis, so that future database reads/writes will happen to the cache as opposed to disk (db writes)?
2. Is SQL Server 2005 recoverable in such configurations?
3. Are there any ideas/resources where I can get more details? (Such as sample configurations with bench mark numbers, rpevious experiences..etc)

Thanks
Murthy

View 1 Replies View Related

MSDE 2000 Replication To SQL Server 2000

Jun 27, 2006

i went through the documentation but i was not clear on following, here is the scenario :-

a Central server is having SQL Server 2000

3 Remote Locations :- Each having 4-5 no of computers, connected on a Lan, and on one of the machines MSDE 2000 will be running.

My Questions are :-

1. Can remote locations, update data locally and send changes (say in every one hour) to central server. If yes, then how ??

2. Same way they can receive updates from Central Server on whatever was updated on Centra Server or on the 3 remote locations. If yes, then How ??

any help will be highly appreciable.

View 8 Replies View Related

SQL Server 2014 :: Change Data Capture - Minimum Latency?

Jan 28, 2015

I am trying to use change data capture to load the data into the secondtable from table 1 which is coming from UI.

What will be the minimum latency??? Can we use incase of latency less than 5 seconds.

View 1 Replies View Related

Check Sql Server 2000 Or Sql Server 2005

Oct 12, 2007

Hi..
I have a web application(ASP.NET 2.0 + C#). I have sql server 2000 as well as sql server 2005.
Several queries in my application work well with the sql server 2005, and the same query fails in the sql server 2000.
How to check if the database is connected to sql server 2000 or sql server 2005 and then call the queries accordingly through the c# code?
Thanks

View 1 Replies View Related

SQL Server Admin 2014 :: Calculating Average Read / Write Latency Across Instance Using Counters

Apr 27, 2015

How you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.

[URL] ...

However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.

object_name counter_name instance_name cntr_value cntr_type
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712

I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time

((default * default[base]) + (internal * internal[base]))/(default[base] + internal[base])

Then to do the calculation over time, I'd use the changes in the calculated numerator and denominator to get the average.

Does this sound like to correct way to get this value? Is there a good way to verify?

View 2 Replies View Related

Replication Between DB2/IBM(MVS) To SQL Server 2000

Feb 9, 2004

Hi,
I wantked know the best tool or way for Replication between SQL server and DB2/IBM(MVS).
Thanks,
Ravi

View 6 Replies View Related

Replication Between SQL Server 2000 And SQL 7.0

Aug 15, 2001

Hi,

I am in between of analysis for Upgradation of SQL server 7.0 to SQL server 2000.

I have few server on SQl server 7.0

I wanted to know , is the replication supported between SQL server 7.0 and SQL server 2000.

a ) from SQl server 2000 to SQL server 7.0
and
b ) from SQL server 7.0 to SQL server 2000.

Both this scenarios will exist.

Thanks in advance
Vikram

View 1 Replies View Related

SQL SERVER 2000 Replication

May 31, 2006

Hello,

I am using MSDE 2000 for replication of my Data. I have one publisher and two subscribers. but i need t filter rows for publications. Problem is that for filtering, i need host_name and in push replication host_name is name of system in which Agents are running but in MSDE all agents are running in publisher system ( distributor System). so everytime for any publication i get only id of server.
i am facing same problem in pull replication also.

Regards,

View 4 Replies View Related

AS/400 To SQL Server 2000 Replication

Mar 23, 2006

Any suggestions on how to replicate from AS/400 to SQL Server 2000?

Data is stored on a AS/400, but applications use a SQL Server 2000 DB. Currently, DTS packages drop the SQL DB, rebuild the tables from a script, copy the data, and then rebuild the indexes as a nightly batch job. Is there a better way to do this? Also is there a clean way to replicate daily transactions as well?

View 1 Replies View Related

Many To Many Replication In Sql Server 2000

Jul 3, 2007

Hi

I'm trying to determine if it is possible to do many to many replication in sql server 2000.

What i basically want is to have n databases share the same basedate (share a common database) and allow updates in any database to be replicated to all the other databases (with a simple conflict resolution, like last update wins).

My goal is total autonomy, without a single point of failure. If any node goes down, the other nodes will continue to work and continue to replicate their data to the remaining nodes. When a node comes back up it will catch up with the over nodes (or get reinitialized if it was a serious crash).

The amount of data i want to replicate is not that big (less than 100MB) and does not change that often. All servers are sql server 2000 instances connected by a gigabit network and the number of nodes involved is less than 10. Some latency is also acceptable.

the question is: is this at all possible? I have read i bit in 'SQL Server High Availability By Paul Bertucci' and some other resources and it looks like a multiple publishers or multiple subscribers with merge replication setup should work, but i'm not too sure if it will work for n > 2 nodes (where all nodes publish and subscribe to each other) and it also mentions constraints on which data a given node is allowed to update (i hope this could be handled by simple conflict resolution).

And if it is not possible in 2000, could it be accomplished en 2005?

TIA Jens

View 3 Replies View Related

How To: SQL Server 2000 Replication?

Feb 8, 2006

Hi,

Can anyone help me?

How do I implement SQL Server Replication?

Where, and how? Do I have to download specific software to do this, or how?

What would be the right option?

Thanks

SJB

View 3 Replies View Related

Replication From Oracle To Sql Server 2000

Mar 3, 2003

Is it possible to replicate from Oracle to SQL Server.

Thanks,

Bianca

View 1 Replies View Related

Replication(Preparation) In Sql Server 2000

Aug 20, 2007

Morning Guys,
If one was preparing to Replicate a database and need to know how much more size would be needed to accomadate the distribution database and its transaction logs etc... what performance counters would you set to gather this information and or tools...?

How to go about finding the average tranaction size.... number of transactions per day.... via profiler.... perfmon....
any suggestions or good sites for replication would be greatly appreciated...
thanks,


thanks,
Jonathan Marshall

View 1 Replies View Related

Replication Between Access And SQL Server 2000

Jan 16, 2002

I have to replicate(bidirectional) data from Access(.mdb) to SQL Server .What are the possible ways of doing it? Are there any known issues?

Thanks in advance.

View 3 Replies View Related

Problems In SQL Server 2000 Replication

Apr 19, 2002

Hello
I am facing a very severe problem in SQL Server 2000 replication and here's the description :
I had setup a replication having Publisher and distribution on the same server. Later I found out that the Subscriber database was incorrect and so I dropped the replication and then disabled it so as to start from fresh.
Now as soon as I start to configure the Publisher/Distributor I get the following error message :
"Error 18483 Could not Connect to ABC Server because distributor_admin is not defined as a remote login at the Server "
Please let me know what can be done to resolve this issue

Please help me to resolve this issue as we need to have the replication latest by Sunday 4/21/2002

Thanks again

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved