June 30, 2015 at 7:11 am
CPU was at 100% and memory was at 97% before you started replication... and you thought that turning on Replication wouldn't have bad effects? Yikes. You have a maxed out system without replication. You need to look at some tuning and possible hardware upgrades before you think about replication.
June 30, 2015 at 8:17 am
Markus (6/30/2015)
CPU was at 100% and memory was at 97% before you started replication... and you thought that turning on Replication wouldn't have bad effects? Yikes. You have a maxed out system without replication. You need to look at some tuning and possible hardware upgrades before you think about replication.
I did not turn it on.
Someone else turned it on late last week and went on vacation the entire week.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 30, 2015 at 8:35 am
Well, this is turning out to be a rather interesting chain. If your server is spiked at 100/100; your issue probably isn't resources...Have a look at the indexes, are the fragmented? How is Disk I/O on the server?
As for your replication; the only time you will ever see it come close to nailing the cpu like that is during snapshot generation. Ensure that the publications have Pull subscriptions disabled, assuming they are not being used. This will allow for concurrent snapshot generation and may reduce any locking issues.
Anyway, hope you get it sorted out. I've spent many a day (and night) dealing with my own replication woes. It can be a very frustrating process.
The best advice I can give is that sometimes...just sometimes the best solution is to bite down hard and tell the business you need an outage. With no data coming in, you can hammer out some new snapshots; reinitialize all subscribers and be home by dawn with everyone up to date and happy.
They don't like it, but in a weeks, a months or a years time; you'll be very happy you did it.
Cheers, best of luck!
June 30, 2015 at 10:54 am
I just inherited this Server and a lot of other things. It has not fully been transferred over to me.
I have checked for fragmentation but I have to wait for the maintenance window.
Solid State Drives were put in two weeks ago.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 30, 2015 at 4:16 pm
A Developer informed me that there was Data in the tables and that they were being replicated.
Then he tells me the tables are empty.
I created a snapshot and that step completed.
The Snapshot completed. The Log Reader Agent is committing Transaction.
The tables were there by but they were not populated.
I'm getting errors that object Status (Table) was not found.
Then none of the files (for each Table are not being sent because they were sent in a previous Distribution.
It appears that someone deleted the Table.
I went and added it as an article then ran the Agent but it did not create the table and I keep getting those errors.
Any ideas?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2015 at 3:25 am
Hi,
It sounds like you are in a bit of a mess.
Are you dealing with replicating to developer servers/machines ? Do they know the rules about replication ? When I say rules I mean dealing with replicated data and schema.
Are you sure your replication is setup correctly ?
Do you understand how replication works ? There are different agents that perform different tasks, some run all the time and some infrequently.
It's doesn't sound like we have a level playing field here so it is hard to offer advice 🙁
Graeme
July 1, 2015 at 5:39 am
Can you check and see how the articles are configured.
I think by default, the article is dropped and recreate from publisher when you push a snapshot.
You may find that with the table that is missing, the article has been set to either delete the destination data or truncate it.. and that is causing it to break.
if you have the replication error, you can browse the replication system tables to actually see what the snapshot script is trying to do.
i.e.
use distribution
go
select * from MSrepl_commands
where command_id = 1
and article_id = 47 -- table name from publisher
and xact_seqno = 0x00156D9100019C6D001300000000 -- this is normally found in the error on replication monitor
-- based on above information result, populate below
sp_browsereplcmds @xact_seqno_start = '00156D9100019C6D001300000000'
--,@xact_seqno_end = '00156D9100019C6D001300000000'
--,@originator_id = 1
,@publisher_database_id = 21 -- publishing database ID
,@article_id = 26 -- Publishing Table ID
,@command_id = 1
if the table is missing, script it from publisher and create it on the subscribing end and see if you can progress to the next error
July 1, 2015 at 6:39 am
When I view the Snapshot Agent Last Action it states that a snapshot was not generated because no subscriptions needed initialization.
The subscriber tables are all empty.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2015 at 7:03 am
I got replication to run.
It added the missing article.
There is no data in any of the tables.
What could I be missing?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2015 at 7:19 am
If you launch replication monitor and go and view the status of the subscription, what does it say?
Is the status "Not Running...."
if so, right click on it and say, reinitialize subscription.
It will ask if you want to use the current snapshot, or create a new one.
I would create a new one, in case you have changed any of the properties on the publisher or articles.
once you click ok, go back to management studio, replication, view snapshot agent status.. see if its running, if not, start it.
Once the snapshot has been created, go back to replication monitor, right click on your subscription and "view details".
You should see the snapshot being applied.
I suspect your tables are empty, because the subscriber is not waiting to be reinitialized
July 1, 2015 at 7:31 am
I would look at this from another angle. What is blocked? What is the lead blocker? Where is the blocking occurring? For example, its common to see blocking on a transactional replication subscriber because the distribution agent is sending data and you most likely have applications or users reading the same data. This is an ideal use case for read committed snapshot isolation because the incoming data is already delayed coming from replication so their should be no real expectation that the subscriber data is exactly real-time.
If you want to disable for short term here is something to consider. You have blocking on the subscriber you could disable the job for distribution agent. I wouldn't disable the log reader job as it could cause bloat in your transactional log. I would also check your settings for your distribution database to see how long it will keep transactions. If you fall out of this range you will have to reinitialize.
July 1, 2015 at 7:45 am
jsterrett (7/1/2015)
I would look at this from another angle. What is blocked? What is the lead blocker? Where is the blocking occurring? For example, its common to see blocking on a transactional replication subscriber because the distribution agent is sending data and you most likely have applications or users reading the same data. This is an ideal use case for read committed snapshot isolation because the incoming data is already delayed coming from replication so their should be no real expectation that the subscriber data is exactly real-time.If you want to disable for short term here is something to consider. You have blocking on the subscriber you could disable the job for distribution agent. I wouldn't disable the log reader job as it could cause bloat in your transactional log. I would also check your settings for your distribution database to see how long it will keep transactions. If you fall out of this range you will have to reinitialize.
Blocking is no longer an issue. I fixed the offending Stored Procedures.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2015 at 7:48 am
I reinitialized and created a new snapshot.
I got messages that the Data was being bulked copied.
There is no data in any of the tables. :sick:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2015 at 7:54 am
I go into the Distributer To Subscriber History and I get Skipping File 'Trades' it has already been delivered for a previous article or a previously interrupted snapshot.
I get this message for every table.
There are no files in the Replication Folder.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2015 at 7:59 am
Welsh Corgi (7/1/2015)
I reinitialized and created a new snapshot.I got messages that the Data was being bulked copied.
There is no data in any of the tables. :sick:
Are you sure its not going to a different schema?
Publisher being "dbo" subscriber might have a different schema, and now its created the tables as "dbo" on the subscriber?
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply