Weird Error during replication - TRUNCATE_ONLY not recognized?

Question

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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 · Answer 2

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.