Using ‘sp_browsereplcmds’ to Diagnose SQL Replication Issues

When diagnosing transactional replication issues in SQL Server, we may need to examine pending commands within the distribution database. In other words, we not only have to monitor these pending commands but also take necessary actions to ensure the replication continues to function. For instance, at times, we may need to terminate specific commands due to errors that prevent other commands from being replicated to subscribers. Before doing so, we must first identify which commands have to be removed from the queue, using the sp_browsereplcmds system stored procedure. This procedure accepts several input parameters, such as article_id.

EXEC SP_BROWSEREPLCMDS @article_id = 1

After executing it, we are going to filter only the pending commands for the Article in question. (Remember that an article in replication is directly related to a table. You can query ‘sysarticles’ system table inside the published database.)


Another parameter we can use in order to get more specific information is the transaction sequence number which is essentially the identifier for the transaction. Luckily, when reading some errors, we can see the sequence number and command ID which allow us to identify exactly the root cause we need to work on with ease.

EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '0x00000027000000B50008',@xact_seqno_end = '0x00000027000000B50008' 

There are other parameters like command ID to get only the command we need to look into, and also the database ID to get all commands for that database.

EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '0x00000027000000B50008',@xact_seqno_end = '0x00000027000000B50008' ,
@publisher_database_id = 33, @article_id = 1,@command_id= 1

Be cautious, do not execute ‘sp_browsereplcmds’ without any parameter on production database environments as they can have millions of commands inside Distribution database and as a result of this we will not get what we need rapidly and at the same time we will affect the database server performance. I hope you can find this post interesting when it comes to troubleshooting replication issues. Let me know any remark you may have. Thanks for reading.

Why is the .txt history detail file of SQL Backup Job not created?

Clearly, we always need to have some kind of history for SQL Backup jobs in order to check whether or not they were executed correctly. I personally use a .txt file to write on it all details of each step executed so that I can use it to diagnose any problem or error behind it. Nevertheless, at times set it up may become something not so easy as the .txt file might not be created during the SQL Backup job execution.


There are some reasons why this may be happening. The first one is because the directory where the .txt file does not exist. It should have been created manually before executing the SQL Backup job. The second reason is because the backup directory is blocked. Surprisingly, you will realise it is with blocked access when you try to open it. One way to get it unblocked is by simply opening the directory and then clicking on “continue” via Windows Explorer.



The third one is because the SQL Agent account does not have permissions on that directory. There must have given Read and Write permission on it and there are some cases where we will need to give explicit permissions to the SQL Agent account on that directory via CMD windows command tool. Undoubtedly, it would be no problem if we are working on only one directory but what would it happen whether we are implementing many SQL Backup jobs? it would become a very tedious job to manage one by one. So, in this case we need some manner to automatize and get them done rapidly. For instance, here I am going to show you a technique to achieve it:

icacls "H:\SQLBackup\FinancialDB\Full" /grant MyDomain\sqlagentAccount:(OI)(CI)F

Now for creating the script to give permission on all necessary directories we can create the code by using this T-SQL (based on reading the directory from Backup Devices whose names are like 'FinancialDB-Full_Database_Backup'):

DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',  'ObjectName',   @sn OUTPUT;
select 'icacls "'+SUBSTRING(physical_name,1, LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name)))+'" /grant '+@sn+':(OI)(CI)F'
from sys.backup_devices where name
ORDER BY name 

All the output results must be executed on CMD tool (as Administrator) and finally after executing the SQL Backup job we will verify that the .txt file was created:

And the history details are inside:


I hope this tip is useful and practical for you. Let me know any remark you may have. Thanks for reading!

The 'SkipErrors' parameter for the Replication Distribution Agent

Having encountered numerous errors in SQL Server Replication, I can confidently say that the majority occur at the replication agent level, such as with the Distribution Agent, Log Reader Agent, Snapshot Agent, Queue Reader Agent, and others. Unfortunately, many of these issues are related to Primary and Foreign Key conflicts, which can take significant time to resolve, either individually or by reconfiguring the replication. To avoid downtime and allow operations to continue while we work on fixing these errors, we can use a helpful option: the 'SkipErrors' parameter. I've successfully used this parameter many times to bypass such issues, and it can also be applied to skip other types of errors as needed.

 
Today's post will show how to use the' SkipErrors' parameter which allows to skip specific errors so that the data synchronization process is not stopped. This parameter is configurable in the profile of the Distribution Agent and has as input the error number we may want to skip.

 
The following picture shows an error (with code 547 related to an Foreign Key issue) in the Distribution Agent process and we see how the transactions are being queued due to this error, consequently, there is a need to fix it so as to allow the rest of pending transactions are moved on. (Distribution Agent reads sequentially the table 'msrepl_commands' to get the command to execute in the subscribers, this means that First in the Queue is the First Out to be moved to subscribers.)



Other common errors where you can use 'SkipErrors' parameter is when there are not some rows in the subscriber to apply the changes (The row was not found at the Subscriber when applying the replicated command). The error code for this case is 20598.
 

I mentioned before that the 'SkipErrors' parameter is configurable inside the Distribution Agent profile and that is what we are going to do right now. Firstly, we need to create a customized profile based on the Default profile and write in the 'Value' column the numbers of the errors (to be skipped)  separated by colons as we can see in the following picture 20598:547.



Having done that, we may have to restart the Distribution Agent. Next time the Distribution Agent starts up, it will load the new customized profile with the error codes to be skipped (ignored). Finally, not only will we verify that many errors are skipped, but also the Distribution Agent is running with no problem.
 

Just to finish writing this post, keep in mind that the transactions with these errors were missed, which means that you will not be able to recover them and may affect your data consistency. In other words, we must use 'SkipErrors' parameter with extreme caution. Let me know any remark you may have. Thanks for reading.

How to query the windows users that access via windows groups from SQL Server

At times while monitoring a database server, we may need to know not only which SQL users are accessing, but also windows users for security and auditing purposes. Frankly, this task may be quite simple to get it completed when all of those users are created and visible inside the database engine. Despite this, it is not a surprise that windows users can access via windows groups which means that DBAs can not see them from SQL Server easily unless we have the manner to list them. Luckily, there is an extended stored procedure named “xp_logininfo” that we need to use to get that information. (The 'xp_logininfo' asks the Active Directory for the windows users.)
First of all, this system stored procedure takes two input parameters. The first one is the windows group name, and the second one is the value ‘members’.  For instance, it lists the windows users that access via the windows group ' MyDomain\SQLProdUsrs'. (You need to have “sysadmin” role to execute it.)

EXEC xp_logininfo  @acctname ='MyDomain\SQLPrdUsrs',  @option='members' 

Naturally, this is perfect if we only have one windows group to query. As time goes by, we are likely to get created more windows groups inside the database engine and in no time we will need to create an specific script to figure out the other windows users. Here is that code.

SET NOCOUNT ON

CREATE TABLE #WindowGroup(

    server_name varchar(100),

    account_name varchar(300),

    type char(8),

    privilege char(9) ,

    mapped_login_name varchar(300) ,

    permission_pathsysname  varchar(300)

 )

DECLARE @WindowGroupName varchar(max)

DECLARE @db [NCHAR](128)  

DECLARE cursor_WG CURSOR FOR SELECT [name] FROM sys.server_principals WHERE TYPE='G' 

OPEN cursor_WG

    FETCH NEXT FROM  cursor_WG INTO @WindowGroupName

    WHILE @@FETCH_STATUS= 0

        BEGIN

            INSERT #WindowGroup(account_name , type , privilege  ,mapped_login_name  ,permission_pathsysname   )

            EXEC xp_logininfo  @acctname =@WindowGroupName  ,  @option =  'members' 

        FETCH NEXT FROM cursor_WG INTO @WindowGroupName

    END 

CLOSE cursor_WG

DEALLOCATE cursor_WG

UPDATE #WindowGroup SET server_name=@@servername

select server_name AS ServerName, account_name as WindowsAccountName, Type, Privilege, mapped_login_name as MappedLoginName, permission_pathsysname as WindowsGroupName

FROM #WindowGroup 

ORDER BY permission_pathsysname,mapped_login_name

DROP TABLE #WindowGroup

SET NOCOUNT OFF

As you have seen, the T-SQL code is simply easy to understand. Clearly, it filters the windows groups by indicating the value ‘G’ for the Type column of the system view “sys.server_principals”, and eventually through the cursor each of them is read. Now I hope you make the most out of this script to have better visibility of the windows users accessing the databases. Thanks for reading again!

SQL Mirroring Port could stop working unexpectedly

When it comes to diagnosing and fixing some SQL Mirroring issues we may have to face with bizarre causes that could stop working a database mirroring and as a result the state of it will be "disconnected". For instance, one of the most common problems is that the SQL mirroring port may stop working unexpectedly. Consequently, the whole database mirroring functionality will stop working too. We basically configure SQL Server mirroring to use the port 5022 by default which works good enough for the vast majority of cases. To be perfectly honest, there is not an urgent need to change it since the issue is not about the port number (or mirroring configuration) but the port itself.

While working on fixing the issue, we may think the database mirroring port may have stopped working or blocked for some security reason which we do not know accurately at the beginning of the situation. Nevertheless, the reason should be investigated further by the Network team so as to prevent from getting the same issue in the future again. Having clear the situation, I am going to show one way to have the database mirroring working again. To begin with, we need to figure out what is the current number port of the mirroring being used.



We can see that the mirroring port is 5022, so we can opt to change it.

ALTER ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5023)

Only after successfully completing that will we have to restart the database engine. Finally, we need to restore the original port and then restart again the database engine.

ALTER ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5022)

Having done it, we will see the database mirroring working properly. That's is all for now. I hope this tip also works for you. Let me know any remarks you may have. Thanks for reading.