Weird Error during replication - TRUNCATE_ONLY not recognized?

• mikel 98376

  Mr or Mrs. 500

  Points: 581

  More actions

  March 5, 2018 at 6:55 am

  #329534

  I have just set up a brand new replication.  
  Both servers are:
  Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

  Both publisher and subscriber databases are set to: 
  Recovery Model: Full  
  Compatibility Level: SQL Server 2012

  In the "Distributor to Subscriber History" tab of the Monitor,  it shows up with this error.

  'TRUNCATE_ONLY' is not a recognized BACKUP option.

  The full error is:
  Command attempted:
  declare @event_type SMALLINT = 1, @distributor_major_version SMALLINT = 11, @distributor_minor_version SMALLINT = 0, @distributor_build_number SMALLINT = 6607, @totalruntime INT = 7891, @totalworktime INT = 4375, @totalnumtrans INT = 0, @numtranspersec REAL = 0.00, @totalnumcmds INT = 0, @numcmdspersec REAL = 0.00, @totalskippedcmds INT = 0, @totalidletime INT = 0 if object_id(N'sys.sp_repl_generate_subscriber_event') is not null exec sys.sp_repl_generate_subscriber_event @event_state = @event_type,@distrib
  (Transaction sequence number: 0x0000A76100001B6F003D00000000, Command ID: 731)

  Error messages:
  'TRUNCATE_ONLY' is not a recognized BACKUP option. (Source: MSSQLServer, Error number: 155)
  Get help: http://help/155

  

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 5, 2018 at 3:38 pm

  #1982023

  mikel 98376 - Monday, March 5, 2018 6:55 AM

  I have just set up a brand new replication.  
  Both servers are:
  Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

  Both publisher and subscriber databases are set to: 
  Recovery Model: Full  
  Compatibility Level: SQL Server 2012

  In the "Distributor to Subscriber History" tab of the Monitor,  it shows up with this error.

  'TRUNCATE_ONLY' is not a recognized BACKUP option.

  The full error is:
  Command attempted:
  declare @event_type SMALLINT = 1, @distributor_major_version SMALLINT = 11, @distributor_minor_version SMALLINT = 0, @distributor_build_number SMALLINT = 6607, @totalruntime INT = 7891, @totalworktime INT = 4375, @totalnumtrans INT = 0, @numtranspersec REAL = 0.00, @totalnumcmds INT = 0, @numcmdspersec REAL = 0.00, @totalskippedcmds INT = 0, @totalidletime INT = 0 if object_id(N'sys.sp_repl_generate_subscriber_event') is not null exec sys.sp_repl_generate_subscriber_event @event_state = @event_type,@distrib
  (Transaction sequence number: 0x0000A76100001B6F003D00000000, Command ID: 731)

  Error messages:
  'TRUNCATE_ONLY' is not a recognized BACKUP option. (Source: MSSQLServer, Error number: 155)
  Get help: http://help/155

  

  That is weird being that those versions couldn't issue that command. Any ideas on what the previous interrupted snapshot is - were there problems with the initial snapshot?
  If you have just set it up I would try starting over with a new snapshot.

  Sue

• mikel 98376

  Mr or Mrs. 500

  Points: 581

  More actions

  March 6, 2018 at 9:48 am

  #1982103

  I've dropped the Publication and I've started over completely.   It says the Snapshot is fine, but still getting the same Truncate Only error.  I tried targeting two different servers as Subscribers.  Both get same error.

  As a test, I created another Publication as well, on a different database and it worked fine. 

  As another test, I created a publication on the same database, as a snapshot, but with only 1 table and it worked fine.

  Third test, I created a Snapshot publication with all 20 tables that I need replicated.  This one also failed with the same error.