jamescool:
And herein lies my problem. I am getting a huge number of errors relating to 'row not found at Subscriber when applying replicated command. (Error: 20598)'.
This exact configuration was working perfectly when the environment was totally SS2K but now we have a mixed environment and they system is failing. This is the reason for the skip errors flags being set.
Do you have any idea as to why these errors are occurring and how to solve them.
Kind regards
James.
The only time I have seen that error before is when someone has done deletes on the subscriber side and removes rows that later get updated on the publisher side. So, you may want to ensure that no one is changing the data on the subscriber side.
Another possibility, though not one I have seen personally, is if rows were created on the publisher and the creation did not get replicated for some reason but later the update statement did replicate.
For both of those, the easiest fix is to reinitialize from a snapshot and validate often. With a 600GB database reinitializing may take some time, so if you cannot wait, there are other tools that can help you bring them back into synch without doing a full reinitializaton.
For validating, what I do personally is use this script I wrote:
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
alter procedure
ValidateDbPublications
@dbname sysname /*should not be
bracketed, quotename is used in procedure */
as
/* Sample:
sp_msforeachdb '
execute ValidateDbPublications ''?''
'
execute ValidateDbPublications
'querytrackingv2'
*/
/*
This will execute
sp_publication_validation against every publication in a database.
It can be used in conjunction with
sp_msforeachdb to execute it against every active publication in a server.
This is designed to use with SQL Server
2005 only.
*/
/************************ Variables
****************************************/
Declare
@HasRepl bit,
@nsql nvarchar(4000), /* Used for dynamic SQL
*/
@nsql2 nvarchar(max) /* Used for dynamic sql generated within dynamic sql.*/
/***************************************************************************/
/* Make sure the database exists */
if not exists (select * from sys.databases where name
= @dbname)
BEGIN
RAISERROR(
N'The database specified does not exist', /* Message */
12, /*Severity */
1 /*State */)
RETURN 1
END
/* This helps shield against sql
injection, as well as names that require quotes */
set @dbname = quotename(@dbname)
/******************** Check to ensure it
has replication*********************************/
set @nsql = N'
if exists
(select * from '
+ @dbname + '.information_schema.tables where table_name =
''syspublications'')
set @HasRepl = 1
Else
set @hasRepl = 0
'
exec sp_executesql
@nsql,
N'@HasRepl bit OUTPUT',
@HasRepl OUTPUT
if @HasRepl = 0
BEGIN
print 'The database ' + @dbname + ' does not have replication enabled. Exiting Procedure.'
RETURN
END
/* From here forward the replication is
assumed. There is no if statement, but it will not reach this point if it
is not enabled. */
set @nsql = '
select
@nsql2 =
''exec ' + @dbname + '.sys.sp_publication_validation '''''' + name +''''''''
from
' + @dbname + '.dbo.syspublications'
exec sp_executesql
@nsql,
N'@nsql2 nvarchar(max) OUTPUT',
@nsql2 OUTPUT
exec (@nsql2)
And I have it in a job that runs it using the undocumented stored procedure sp_msforeachdb. That way it will issue a validation command for every database that has a publication and ignore the others.
Of course, that is most useful if you have alerts set up to notify you if a validation fails.
I hope this helps.
TimothyAWiseman@gmail.com