August 29, 2011 at 3:19 pm
Does anyone know if there's a way around generating the ENTIRE snapshot (for all currently included articles) for transactional replication?
We have an extremely "busy" OLTP environment and currently employ replication for about 300 tables (there are a couple thousand total). Every now and then we'll receive a request to replicate a new table here and there.
Adding that one darn table results in needing to run the Snapshot Agent to push over the changes to the reporting datawarehouse...this creates over 122GB of bcp files and so forth (stored out on a SAN).
This severely bites when I only needed to replicate a table with about 180MB of data in it.
SURELY there has to be a way to only generate a snapshot for the new article???
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 30, 2011 at 1:11 am
open the properties for that publication---> Add your table--> Press OK and come out of that wizard.
now, go to the replication and right click on your publication--> View Snapshot agent status
It will show you the last snapshot generated (say for 300 articles). At this point, just start the snapshot agent. (Do not click on checkbox saying 'Reinitialize'). Simply run the snapshot agent and you will find that the snapshot for only 1 table has been generated.
I suggest you to try this in your test env. first.
thanks
Chandan
August 30, 2011 at 2:19 am
chandan_jha18 (8/30/2011)
It will show you the last snapshot generated (say for 300 articles). At this point, just start the snapshot agent. (Do not click on checkbox saying 'Reinitialize'). Simply run the snapshot agent and you will find that the snapshot for only 1 table has been generated.
You're quite right about the snapshot but in a busy OLTP environment I wouldnt do this through SSMS. Use TSQL for control.
Use sp_addarticle and sp_addsubscription then manually run the SQL Agent job on the distributor.
Dont worry about passing @article ='all' to sp_addarticle this is required and the proc is intelligent enough to know the articles already subscribed and add an additional one.
I do this all the time and its fine for both push and pull subscriptions.
August 30, 2011 at 3:21 am
August 30, 2011 at 8:00 am
I am using SQL 2008 EE, and do not see a checkbox for 'Reinitialize'...
I have only buttons to Start, Stop, Monitor, and Close
I will try the TSQL...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 30, 2011 at 12:06 pm
MyDoggieJessie (8/30/2011)
I am using SQL 2008 EE, and do not see a checkbox for 'Reinitialize'...I have only buttons to Start, Stop, Monitor, and Close
I will try the TSQL...
Leave the re-initialize button. Just start the snapshot agent. You will see that you got snapshot generated for only 1 article.
Before you use T-SQL instead of GUI, make sure you learn and understand each parameter in the command. GUI is easier to use for this task, IMHO but nevertheless T-SQL knowledge is always useful.
August 30, 2011 at 1:10 pm
chandan_jha18 (8/30/2011)
MyDoggieJessie (8/30/2011)
I am using SQL 2008 EE, and do not see a checkbox for 'Reinitialize'...I have only buttons to Start, Stop, Monitor, and Close
I will try the TSQL...
Leave the re-initialize button. Just start the snapshot agent. You will see that you got snapshot generated for only 1 article.
Before you use T-SQL instead of GUI, make sure you learn and understand each parameter in the command. GUI is easier to use for this task, IMHO but nevertheless T-SQL knowledge is always useful.
IMHO they shouldnt be changing anything in a "busy" production environment without control and knowing what the tsql is going to do behind the scenes. Time and again I've proven that SSMS is a useful tool but does many things you dont expect it to. By all means use it but cautiously in a busy environment where you cannot guarantee what it will do.
FWIW our environment runs around 8 million replicated commands per hour at peak times.
April 26, 2012 at 4:04 am
Thanks ,it worked for me
April 26, 2012 at 4:31 am
naralas.srikanth (4/26/2012)
Thanks ,it worked for me
glad to hear that. What was the issue you were facing and how this post solved your problem. We would liek to hear it so that it can help someone at a later stage.
Thanks
Chandan
April 26, 2012 at 6:24 am
If you set up transactional replication using the GUI, it does quirky things behind the scenes (like automatically set settings you really don't want). To ensure you don't take snapshots of everything after you drop/add a new article, check that the immediate sync and allow anonymous are SET TO ZERO by running the code below (this is to be run on the distribution server):
/* On the distributor run to verify the publishers - make CERTAIN allow anonymous/immediate_sync are all ZEROS */
SELECT publisher_id, publication_id,
publisher_db,
publication, allow_anonymous, immediate_sync
FROM distribution.dbo.MSpublications WHERE publication_type = 0 --> 0 for transactional, 1 for snapshot
If they are not zero, run something like this:
USE YOURDB
GO
EXEC sp_changepublication @publication = N'MyRepl_TABLES', @property = N'allow_anonymous', @value = 'false'
GO
EXEC sp_changepublication @publication = 'MyRepl_TABLES', @property = N'immediate_sync', @value = 'false'
GO
Rerun the first SQL above to ensure the results are all ZERO
Either using the GUI or using the TSQL below you can manually drop/add articles
use [YOURDB]
exec sp_dropsubscription @publication=N'MyRepl_TABLES', @article=N'TableToDrop', @subscriber=N'all'
exec sp_droparticle @publication = N'MyRepl_TABLES', @article = N'TableToDrop', @force_invalidate_snapshot = 1
IMHO, you should stop the log reader agent for the next steps (but others have said it isn't needed, I still do it anyway)...
If you are removing an article, you're complete done at this point. You can run this code on the distribution server to verify (Where "X" is the values for your publication_id):
/* Verify the article has been deleted */
select * from distribution.dbo.msarticles
WHERE publication_id =XXX AND article = 'TableToDrop'
select * from distribution.dbo.mssubscriptions
WHERE publisher_database_id = XXX and article_id = 66 --> Returned from above query
/* Verify there are no replication commands in existence for the dropped articles */
SELECT COUNT(1)
FROM distribution.dbo.MSrepl_commands c WITH(READUNCOMMITTED)
WHERE publisher_database_id = XXX and article_id = 66
/* Wipe the tables clean - THIS IS TO BE DONE ON THE SUBSCRIBER!!! */
TRUNCATE TABLE [YOURDB].[dbo].TableToDrop
If you are adding a new article, Right click on the publication, then click on "View Snapshot Agent Status". Click Start. This will start the snapshot for the article(s) you created.
Once the snapshot agent has completed, turn the log reader agent back on for the publication you turned off.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 27, 2015 at 10:02 am
When you add a table in and press ok how long should that take?
January 27, 2015 at 10:24 am
If you didn't check the box to generate the snapshot immediately then it should be relatively quick, it you generated the snapshot, it would depend on the amount of data you creating in those delightful bcp files to the file system 🙂
So if you didn't generate the snapshot, check to see if something's blocking your process.
Here's a script to help you figure out the size of the snapshot (just change it to find your table) SELECT pubname ,
SUM([Total(MB)] ) / 1024 'Total(GB)'
FROM ( SELECT DISTINCT
a .pubid ,
a .name AS PubName ,
OBJECT_NAME(i .id) AS TableName ,
MAX(i .[rows]) AS 'Rows' ,
CONVERT(dec (10, 3), SUM(i .reserved * 8.000 / 1024 )) AS 'Total(MB)'
FROM dbo. syspublications a WITH ( NOLOCK )
INNER JOIN dbo.sysarticles b WITH ( NOLOCK ) ON a .pubid = b.pubid
INNER JOIN dbo.sysobjects o WITH ( NOLOCK ) ON b.dest_table = o.name
INNER JOIN dbo.sysindexes i WITH ( NOLOCK ) ON o.id = i. id
WHERE i. name NOT LIKE '_WA_Sys_%'
AND i. indid IN ( 0, 1, 3 , 255 )
AND o. type = 'U'
--and a.name like 'ISET%'
GROUP BY a .pubid ,
a .name ,
OBJECT_NAME(i .id) ,
i .indid
) a
GROUP BY a.PubName
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 27, 2015 at 10:40 am
What checkbox is this? I am trying to add an article to an existing publication as per
https://technet.microsoft.com/en-us/library/ms152493(v=sql.100).aspx
January 27, 2015 at 10:50 am
Sorry that's only if you were using the GUI...
What is the spid currently doing?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 28, 2015 at 2:19 am
I am using the GUI.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply