Sql Server : Replication : Length of LOB data (65536) to be replicated exceeds configured maximum 131072. The statement has been terminated

Quick one today:

Every once in a while, this error pops up in production environments, usually after application software upgrade. Error message looks something like this:

Length of LOB data (65536) to be replicated exceeds configured maximum 131072.
The statement has been terminated.

Essentially, what happened is that the size of LOB data that is being replicated has exceeded the upper limit settings. So Sql Server is complaining that in encountered sizes larger than what is is configured to replicate. There is an easy fix.

--
--  Increase the maximum size of text to replicate
--
EXEC sys.sp_configure N'max text repl size (B)', N'150000'
GO

RECONFIGURE WITH OVERRIDE
GO

max text repl size (B) indicates the maximum size in bytes that could be replicated in a single statement like INSERT UPDATE, etc.

For example, in our situation, we have a table with XML column. If the size of the XML file goes beyond the default size of 65536  bytes, this error occurs when that record is replicated.

As it is, XML columns can accept XML files larger than 65536 bytes during INSERT, UPDATE, etc; But when this row (new INSERT or UPDATE) needs to be replicated to Subscriber, the replication infrastructure may not be able to handle it as the upper limit was configured to 65536 bytes. As we saw above, it is easy to change.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Replication Error :The process could not execute ‘sp_replcmds’ on. Could not obtain information about Windows NT group/user , error code 0x534′

Quick one today:

Recently, on a lab machine, this error occurred that was interesting to solve.

We set up Transactional Replication with Remote Distributor between three lab machines. During the very first initialization, the Snapshot Agent ran fine, but the LogReader Agent failed with this error message:

Error Message:

The key section of the error message is highlighted in red. Of that the one in purple bold is the root cause of the issue.

Message
 2016-06-13 18:54:26.010 Copyright (c) 2008 Microsoft Corporation
 2016-06-13 18:54:26.010 Microsoft SQL Server Replication Agent: logread
 2016-06-13 18:54:26.010
 2016-06-13 18:54:26.010 The timestamps prepended to the output lines are expressed in terms of UTC time.
 2016-06-13 18:54:26.010 User-specified agent parameter values:
 -Publisher MachineName
 -PublisherDB test
 -Distributor MachineName\DIST
 -DistributorSecurityMode 1
 -Continuous
 -XJOBID 0x27C9980843371B4E9C1DBAB6CAC1899E
 -XJOBNAME MachineName-test-10
 -XSTEPID 2
 -XSUBSYSTEM LogReader
 -XSERVER MachineName
 -XCMDLINE 0
 -XCancelEventHandle 0000000000000600
 -XParentProcessHandle 0000000000000604
 2016-06-13 18:54:26.120 Status: 32768, code: 53044, text: 'Validating publisher'.
 2016-06-13 18:54:26.120 Parameter values obtained from agent profile:
 -pollinginterval 5000
 -historyverboselevel 1
 -logintimeout 15
 -querytimeout 1800
 -readbatchsize 500
 -readbatchsize 500000
 2016-06-13 18:54:26.135 Status: 4096, code: 20024, text: 'Initializing'.
 2016-06-13 18:54:26.135 The agent is running. Use Replication Monitor to view the details of 
this agent session.
 2016-06-13 18:54:26.151 Status: 0, code: 20011, text: 'The process could not execute 'sp_replcmds' 
on 'MachineName'.'.
 2016-06-13 18:54:26.151 The process could not execute 'sp_replcmds' on 'MachineName'.
 2016-06-13 18:54:26.151 Status: 0, code: 15404, text: 'Could not obtain information 
about Windows NT group/user 'database_login', error code 0x534.'.
 2016-06-13 18:54:26.151 Status: 0, code: 22037, text: 'The process could not 
execute 'sp_replcmds' on 'MachineName'.'.

Solution

Turns out that the publication database, which was created several years ago by a different employee, has his login as the owner of the database. Since that login does not exist anymore, the Log Reader was having problems retrieving data from transactional log (something like it). As if not, it’s not yet clear, what role does the owner of a database plays in LogReader agent, but that is for a later blog post.

Once we changed the owner of the database to a valid login, LogReader agent worked.

--
-- Change the name of the database owner
--
EXEC sp_changedbowner 'sa'
GO

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server 2016 : Download Developer Edition for Free

Quick one today:

Great News!! Sql Server 2016 is out and available for free download. Microsoft has made Developer Edition free to download. Developer Edition will have all the features of Enterprise Edition. There are two ways to get it:

MSDN Account

If you already have MSDN subscription, you could log in and it would be available there.

Others

Otherwise, it is available on Visual Studio’s website for free download. You might need to create an account first.

 

Hope you get to download and play with it.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Active/Active, Active/Passive what do they mean? Terminology confusion.

Quick one today:

For many years, we’ve heard Active/Active & Active/Passive terms in the context of Failover Clustering; They have an implied understanding of what each means. As technology evolves and reaches to greater robustness & availability architectures/deployments, these terms have become misleading and ambiguous.

Active/Active could also imply that some load balancing is occurring across the nodes, which is not the case.

Newer versions of Failover Clustering architectures include running multiple FCI on a single node; And potential of multiple (more than 2) nodes in a single Cluster. So these terms Active/Active or Active/Passive become ambiguous, imperfect & misleading.

Since these terms have been in general usage for several years, its hard to kill old habits. But its worthwhile to adapt and embrace complexity of newer technologies and their terminology. One thing that helped me was to refer to each failover cluster instance separately with details of where it is running.

More applicable terminology would be something like:

• Single Failover Cluster Instance
• Multi-Instance Failover Cluster
• Multi-Site Failover Cluster
• Guest Failover Cluster

For more information, please refer to this white paper from Microsoft.

Just a rant…

Hope this helps,
_Sqltimes

Read Full Post »