May 12, 2009 at 3:42 am
Dear All,
We had setup with the Transactional replication for our organization and we have some problem during the setup... like
1.) We have one database server which access from 2 application server.
2.) One application running the ETL job which copy data from other server and dump in to the publisher database. this will take around 3 hr to transfer the data. During the tran. replication around 25-30 million records are access read in the log file.
3.) from the other application server one table is modifying the table during that time ETL is running so, i found the deadlock on table.
4.) our main scenario to when ETL job is running that time we need to pause or stop the replication.
i had done using sysjobs to deactivate the running replication job. but no used...
Thanks,
Amey
🙂 🙂 🙂
Amey ghag
<a href='http://' class='authorlink' target='_blank'></a>
May 12, 2009 at 7:43 am
You should be able to disable them via the jobs. Can you explain what problem you had doing it that way?
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 12, 2009 at 7:55 am
I think the OP wants to do it as part of the Script.
-Roy
May 12, 2009 at 8:05 am
Roy Ernest (5/12/2009)
I think the OP wants to do it as part of the Script.
Thanks Roy.
The following procedure will stop the replication jobs for a subscriber. You have to get the subscriber_id for your subscriber and change that value in the procedure. Should work pretty well though. This is part of a group of scripts that I have to do this so, if there are pieces missing send me a private message and I will send all the scripts.
USE MSDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:David Benoit
-- Create date: 07/14/2008
-- Description:Procedure to allow the test group to disable replication to SERVERA
-- =============================================
IF EXISTS
(SELECT NAME FROM SYS.OBJECTS WHERE NAME = 'USP_REPL_STOP_SERVERA')
DROP PROCEDURE USP_REPL_STOP_SERVERA
GO
CREATE PROCEDURE USP_REPL_STOP_SERVERA
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@JOBNAME VARCHAR(64)
, @SCHEDULEID INT
, @FREQTYPE INT
DECLARE CUR_JOB_NAME CURSOR FOR
SELECT
DA.NAME
, SS.SCHEDULE_ID
, SS.FREQ_TYPE
FROM
DISTRIBUTION.DBO.MSDISTRIBUTION_AGENTS DA JOIN MSDB..SYSJOBS SJ
ON DA.JOB_ID = SJ.JOB_ID
JOIN MSDB..SYSJOBSCHEDULES SJS
ON SJS.JOB_ID = SJ.JOB_ID
JOIN MSDB..SYSSCHEDULES SS
ON SJS.SCHEDULE_ID = SS.SCHEDULE_ID
/*Again, need to get SUBSCRIBER_ID from replication tables. */
WHERE
SUBSCRIBER_ID = 21
ORDER BY
DA.NAME
OPEN CUR_JOB_NAME
FETCH NEXT FROM CUR_JOB_NAME INTO @JOBNAME, @SCHEDULEID, @FREQTYPE
WHILE @@FETCH_STATUS = 0
BEGIN
/*
Test FREQ_TYPE to see if this is a job that runs continuously. See BOL
reference for sysschedules table for details. Actions required to "pause"
replication vary based on the replication job schedule.
*/
IF @FREQTYPE = 64
BEGIN
EXEC MSDB..sp_stop_job @JOBNAME
EXEC MSDB..sp_update_job @JOB_NAME = @JOBNAME, @ENABLED = 0
END
IF @FREQTYPE != 64
BEGIN
EXEC MSDB..sp_update_schedule @SCHEDULE_ID = @SCHEDULEID, @ENABLED = 0
END
FETCH NEXT FROM CUR_JOB_NAME INTO @JOBNAME, @SCHEDULEID, @FREQTYPE
END
CLOSE CUR_JOB_NAME
DEALLOCATE CUR_JOB_NAME
END
GO
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 13, 2009 at 5:17 am
Thanks, David for your post. now i m able to stop the replication druing my ETL.
:-):-D
🙂 🙂 🙂
Amey ghag
<a href='http://' class='authorlink' target='_blank'></a>
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply