question

askmlx121 avatar image
askmlx121 asked

Replication error of Non logged agent shutdown error how to resolve this error?

hi we are using snapshot replication in SQL agents, we are using SQL 2005. but we have receive below error on replication. i am new to this so any one suggest me what kind of error is this? and how to resolve. i checked checkident on table it is also correct. so cant able to point the error. i bolded the keyword of some error for easy anlyse of readers. orginal error is see below: > Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 11/03/2012 06:15:25,LIVEDBNAME Copy to SourceDB,Error,0,liveservername,dbnamelive Copy to destinationdb,(Job outcome),,The job failed. The Job was invoked by User akumar. The last step to run was step 3 (**Detect nonlogged agent shutdown**.). The job was requested to start at step 1 (Distribution Agent startup message.).,00:00:26,0,0,CSC_Operators,,,0 11/03/2012 06:15:51,LIVEDBNAME Copy to SourceDB,Unknown,3,liveservername,dbnamelive Copy to destinationdb,Detect nonlogged agent shutdown.,,Executed as user: NT AUTHORITY\SYSTEM. The step succeeded.,00:00:00,0,0,,,,0 11/03/2012 06:15:25,LIVEDBNAME Copy to SourceDB,Error,2,liveservername,dbnamelives Copy to destinationdb,Run agent.,,The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.,00:00:26,0,0,,,,0 11/03/2012 06:15:25,LIVEDBNAME Copy to SourceDB,Unknown,2,liveservername,dbnamelive Copy to destinationdb,Run agent.,,2012-12-03 11:15:51.186 Category:NULLSource: Microsoft SQL Native ClientNumber: Message: Batch send failed2012-12-03 11:15:51.186 Category:NULLSource: Microsoft SQL Native ClientNumber: 2627Message: **Violation of PRIMARY KEY constraint 'PK_tablename**'. **Cannot insert duplicate key in object 'dbo.tablenamee**'.2012-12-03 11:15:51.202 Category:NULLSource: Microsoft SQL Native ClientNumber: 20253Message: To obtain an error file with details on the errors encountered when initializing the subscribing table execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options.2012-12-03 11:15:51.202 Category:NULLSource: Number: 20253Message: bcp "db"."dbo"."tablename" in "\\liveservername\ReplData\unc\**liveservername_dbname_dbname SNAPSHOT TO otherserver\20121201020034\tablename_13#6.bcp**" -e "errorfile" -t"\n\n" -r"\n<@g>\n" -m10000 -Sdestinationdb -T -w,00:00:26,0,0,,,,0 ***finally what is non logged agent shutdown means????*** Note: I replicace source db and destination db, server with original name for security purpose furthur i have gave the snapshot from replication monitor for understand.........plz help meeeeeee............![alt text][1] [1]: /storage/temp/506-non+logged+agent+shutdown+error+on+replication.jpg **can i Reinitialization MS SQL Replication Subscirber may fix the issue?**
sql-server-2005replication
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
After I Reinitialization on fresh snap shot it solves the problem thanking you..........all
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
"Detect nonlogged agent shutdown", is the output of sp_MSdetect_nonlogged_shutdown is the 3rd job step in your replication agent job, this one logs any errors to sql server error logs and replication error log table when an agent runs. Are you using merge replication? If so check your Identity range constraints on table "tablenamee" on both publisher and subscriber are not overlapping, if they have incorrect values when the agent runs it will end up in PK violation error. I have experienced this error when one my identity range in a subscriber was not updated properly or reseeded incorrectly.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If I use snapshot replication means what is the solution??????????????
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
As you are running snapshot replication the system will basically drop all articles and recreate them. The issue seems to be in the data coming from the publishing system for the table getting the PK constraint violations. I suggest you take a look at the table and make sure that everything is fine there, it should be if there is a PK defined on the source table. Are there any pre-snapshot steps defined that maybe cause the error to occur? It could be that a script is being run and causing something unexpected. Reinitialising the subscription may fix things if the current snapshot is broken somehow, trying it cannot hurt (although be aware that creating a fresh snapshot can be resource intensive).
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
hi william durkin thanking you i checked the source table and destination table identitys are correct Source: Checking identity information: current identity value '2731527', current column value '2731527'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Destination: Checking identity information: current identity value '2730626', current column value '2730626'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. then there is primary key in both source and destination table. so what is the problem? what is the error of previous articles already submitted??? i gave the replication monitor screen shot for furthur analysis. see below here ![alt text][1] [1]: /storage/temp/508-replication+monitor+screen+shots.jpg

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.