December 1, 2011 at 4:36 am
Hi All,
I want to ask your recommendations regarding a issue I am currently experiencing?
I have a SQL2005(server2) and SQL2008(server1) server.
I am running transactional replication between these servers.
The SQL 2005 server is offsite in another country so the connection is rather slow between them.
I am running a replication publication on the SQL 2005(server2) server and the SQL2008(server1) server connect to it as a subscriber.
There is only one article in this replication that is being moved from the SQL2005(server2) to the SQL2008(server1). This table get a lot of data from our website, but we don’t need data older than 3 months, but we need to keep it on the server.
So there was a archiving job setup on the SQL2008(server1) server the remove old data from the tables.
Now a job was created that is scheduled to run once a month. Step1 run on the SQL2008(server1) first then on the SQL2005(server2)
The same stored procedure was created on both servers that remove the old data.
Below is the stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ArchiveTable1]
AS
DECLARE @ArchiveTable VARCHAR(128),
@sql VARCHAR(1000),
@date VARCHAR(6)
--GET THE MONTH TO ARCHIVE
SET @date = CONVERT(VARCHAR(6),DATEADD(mm,-3,getdate()),112)
--SET THE ARCHIVE TABLE NAME
SET @ArchiveTable = 'Table1__' + @date
--PREPARE SQL STATEMENT
SET @sql = 'BEGIN TRANSACTION' + CHAR(13) + 'SELECT * INTO ' + @ArchiveTable + ' FROM Table1 WHERE CONVERT(VARCHAR(6),logDate,112) = ''' + @date + '''' + CHAR(13) + 'COMMIT TRANSACTION' + CHAR(13) + 'BEGIN TRANSACTION' + CHAR(13) + 'DELETE FROM Table1 WHERE CONVERT(VARCHAR(6),logDate,112) = ''' + @date + '''' + CHAR(13) + 'COMMIT TRANSACTION'
--EXECUTE THE ARCHIVING QUERY
EXEC(@SQL)
The LogDate is a column in the table that is updated every time a record get inserted into the table.
Every time this job run once a month this causes the replication to break.
If I change the job step to run on the SQL2005(server2) first then on the SQL2008(server1) will it make a difference and not break the replication?
I am not sure what else to do regarding this job and the replication that is breaking because of this cause a lot of other processes on the production side to fail.
Thank you!
December 1, 2011 at 4:47 am
Reversing job may help .... you can try if you have a test env....
December 1, 2011 at 5:12 am
I will build a test environment and have a look thanks...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply