Friday, September 14, 2018

Exception: Length of LOB data (96550) to be replicated exceeds configured maximum 65536.

We got below exception recently as Lengh of LOB data to be replicated increased the configured limit 65536

Exception Thrown In InboundMessageProcessor ProcessExternalXML Method with [Error]: Internal Xml Parser Failed. Aborting Database Transaction. Current XML Node <FIInvocationSynchronousEvent>, Depth 1.

[InternalXMLParser Error]: Failed to execute business component method. Assembly: FlexNet.SVL.BusinessFacade.Utility, Version=1.0.0.0, Culture=neutral, PublicKeyToken=33f692327842122b, Class: FlexNet.SVL.BusinessFacade.Utility.TableAction, MethodName: TableUpdate, Exception: Length of LOB data (96550) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type.
The statement has been terminated.

Run below command to find the falue

select * from sys.configurations
where name like 'max text repl size%';
GO

You can configure the ‘max text repl size’ to unlimited by using below command
EXEC sp_configure 'max text repl size', -1 ;
RECONFIGURE;
GO

You should be able to see updated value now.
select * from sys.configurations
where name like 'max text repl size%';
GO


I see few users complained that’s this setting lost, you can use ‘OVERRIDE’ option.
EXEC sys.sp_configure N'max text repl size (B)', N'-1'
GO
RECONFIGURE WITH OVERRIDE
GO

Regards
Satishbabu Gunukula
http://sqlserver-expert.com

Thursday, September 6, 2018

Database "Not synchronizing / suspect" in Always On High Availability Groups

We recently configured Always On with 2 secondary servers. One secondary is synchronous commit and another with Asynchronous commit.

We are running log backup but noticed that secondary couldn’t redo the log at secondary and kept on piling the log and the drive got full.

Run below command to see what log is not clearing out
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'HRDB';

Name    log_reuse_wait_desc
--------  -------------------------------
HRDB    AVAILABILITY_REPLICA

For short term add space or you might have to remove database from AlwaysOn Group and add it back. 

In the log we see “Could not redo log record” message 

Could not redo log record (40557:30625:14), for transaction ID (0:196794283), on page (1:1033), allocation unit 72058591529205760, database 'HRDB' (database ID 7). Page: LSN = (40557:30379:326), allocation unit = 72058646460891136, type = 10. Log: OpCode = 7, context 10, PrevPageLSN: (40557:30625:12). Restore from a backup of the database, or repair the database.

After troubleshooting it looks like a Bug and need to apply Cumulative patch

https://support.microsoft.com/en-us/help/3173471/fix-could-not-redo-log-record-error-and-replica-is-suspended-in-sql-se

Thanks
Satishbabu Gunukula
http://www.sqlserver-expert.com