Replication Troubleshooting

Basics of Replication Troubleshooting

There are many scenarios where you have been alerted for the replication failure and you have to troubleshoot the issue. In this article I will guide you what should be your approach to get the detailed error message and transaction details in replication.

First check the replication monitor and click on the failed publisher. Next step is double click on the failed subscriber from All Subscriptions list.

Now next step is click on the error and check its description.
Error :
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000044100002D93000100000000, Command ID: 1)

From the above error message we have to identify which command is failed to execute on the subscriber.

To get the exact command, find out the distributer server and distribution database for the failed publisher.

Once you get the distribution database server, execute the below query against the distribution DB.

1 2 3 4 5

use distribution go SELECT * FROM msrepl_commands WHERE xact_seqno = 0x0000044100002D93000100000000 AND command_id = 1

Once you execute the above query against the distribution database, you will get the more information about the error, for example Publisher database ID, Article ID and much more…

We have to use the above details, to get the exact command using eitherSP_BROWSEREPLCMDS (If CLR is enabled) or you can cast the command column inmsrepl_commands table.

We will check both the alternatives.

Using SP_BROWSEREPLCMDS 
Please note CLR must be enabled for to use this procedure.

1 2 3 4 5 6

EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '0x0000044100002D930001', @xact_seqno_end = '0x0000044100002D930001', @publisher_database_id = 1033, @article_id = 12, @command_id= 1

By casting command column in msrepl_commands table

Please note if you want to see the better output use the Result to Text as output in SSMS (CTRL + T)

1 2 3 4

SELECT CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX)) FROM msrepl_commands WHERE xact_seqno = 0x0000044100002D930001 AND command_id = 1

Now you got the exact SQL Command. As a next step check the objects from both the publisher and the subscriber to see the violation of the keys or do the data comparisons etc.

=================================================================================

Row Not Found at the Subscriber - Replication Issue

 

When you find an issue in replication with the error “The row was not found at the Subscriber when applying the replicated command.”, first we have to get the Transaction sequence number and Command ID from the error.

This can be found at Distributer to Subscriber history in replication monitor.

Once we get the Transaction Sequence Number and Command ID we can easily drill down to the command which is causing the issue by using sp_browsereplcmds. Before to this, we have to also find out publisher_database_id.

For finding publisher_database_id, we need to make use of Transaction Sequence Number and Command ID.

Query to find publisher_database_id using Transaction Sequence Number and Command ID

select * from msrepl_commands where xact_seqno = 0x000BF8FB0003411E000400000000 and command_id=6

Once we get the publisher_database_id from the above query, then we need to execute the below query to get the command which is causing the error.

Query to find the command which is causing error

exec sp_browsereplcmds @xact_seqno_start = '0x000BF8FB0003411E000400000000', @xact_seqno_end = '0x000BF8FB0003411E000400000000', @Command_id=6,@publisher_database_id=60

Once we get the command, we can manually sync the missing data from publisher to subscriber to make the replication work fine as before.

Note: All these commands have to be run on distribution database.

This article is also available in pdf format for downloading.

Please Click here to get your copy.

=======================================================

DIFFERENCES BETWEEN TINYINT , SMALLINT , INT , BIGINT IN SQL SERVER

DIFFERENCES BETWEEN TINYINT , SMALLINT & INT , BIGINT IN SQL SERVER
Similarities and Differences.

1.TINYINT

Minimum Value : 0
Maximum Value : 255
Storage size : 1 byte

2.SMALLINT

Minimum Value : -32768
Maximum Value : 32767
Storage size : 2 bytes

3.INT

Minimum Value : -2^31
Maximum Value : 2^31 – 1
Storage size : 4 bytes

4.BIGINT

Minimum Value : -2^63
Maximum Value : 2^63 – 1
Storage size : 8 Bytes.

SECURITY QUESTIONS: GRANT PERMISSIONS TO ALL STORED PROCEDURES / Allow user to EXEC and EXCUTE command in sqlserver

--Listing 1. Grant exec to all stored procedures in a database
GRANT EXEC TO [User]
GO

--Listing 2. Permissions based on executing stored procedures in a schema

GRANT EXEC ON SCHEMA::SomeSchema TO [User]

For Eg:

USE EmpDb
GRANT EXEC TO Selva;
GRANT EXECUTE TO Selva;

Unused Index Script

SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

Add new article in transactional replication in SQL Server 2012

Add new article in transactional replication in SQL Server 2012
Creating replication is one thing and maintaining it is a different yet equally important thing. Over the time, you might add or remove some objects in your primary database and hope the same for your replication database and this article comes into the picture when you need to add some more object from primary (Publication) database to replicated (subscriber) database.



Step 1 :













Step 2:






step 3 :

Copy And Paste From SQL Server Management Studio 2012 New Line Issue Into Excel

select REPLACE(REPLACE(col, CHAR(13), ''), CHAR(10), ' ')




for eg : 

OrderStatus=REPLACE(REPLACE(cast(OrderTxtStatus as varchar(500)), CHAR(13), ''), CHAR(10), ' ')

sql server Replication and Move Database Files mdf and ldf to Another Location

Step 1 :  Stop log agent in primary DB replication located

Step 2 : Open Read or secondary DB , new query window --->USE MASTER;

Step 3 :  Run 

-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE db_read
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Step 4 : -- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'db_read'
GO

Step 5 : Manually move the mdf and ldf Files from Loc1 to Loc 2

Step 6 : -- Re-Attached DB
CREATE DATABASE [db_read] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read_log.ldf' )
FOR ATTACH
GO

Step 7 : start the log agent in primary DB