Thursday, 30 June 2016

Error 20598: The row was not found at the Subscriber when applying the replicated command

Having transactional replication environments with read-only subscribers (which means that changes are not propagated back to the publisher), it is very important to understand that rows on subscribers must NOT be modified directly. Otherwise, we will get a big problem. Let me expand on what I am saying. For instance, if any row on the subscriber (which was replicated from publisher) is deleted and then when this same row on published is modified, the following error will be raised:
The row was not found at the Subscriber when applying the replicated command
Clearly, this issue is because the row to be updated on subscriber does not exist any longer while Distribution Agent is trying to propagate it. Therefore, there is a need to fix it as soon as possible to prevent the replication queue from growing so much. To solve this case, we must review the pending commands inside the 'distribution' database by using 'sp_browsereplcmds' in order to identify the affected transaction(s) and row(s), and then insert the missing row manually in the subscriber (or delete the command from queue, however, this recommendation can be taken into account only if someone deleted the row by mistake or you do not need it anymore).

Another technique we have is to use the 'SkipErrors' parameter which allows to skip errors of a certain type (for this issue the error number is 20598), which means that the affected transaction is simply ignored and skipped. Keep in mind that these sorts of error must be treated with extreme caution and a correct understanding of the situation.

That is all for now, let know any remark you may have. Thanks for reading.

Thursday, 2 June 2016

How to move the files of database which has Replication, Mirroring, Log Shipping or AlwaysOn Settings

One of the challenging tasks in the life of a DBA is definitely moving all or some of the files of a database from one physical location to another one because of performance issues, maintenance requirements, disk space issues, etc. We usually move database files to another location by using Backup/Restore or Detach/Attach procedures. They are the most proper methods for most of the business cases but not for all. Let me expand on what I mean, for instance, those methods will not work with databases which have Replication, Mirroring, Log Shipping or AlwaysOn Settings because you will have to remove these settings before move them and then you should set up every setting again which could waste your time and have your database service stopped further than necessary. In this situation Backup/Restore or Detach/Attach simply is NOT an option because we need to make the database available as soon as possible. So, what we must do in order to move files of this type of database is by modifying the physical name of each database file we want to move. For instance, in the following code I will move 4 files (3 Data Files and 1 Log File):

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data01', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data01.mdf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data02', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data02.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data03', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data03.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Log', FILENAME= N'E:\SQLLog\SalesDB\SalesDB_Log.ldf')


It is very important to verify that new database file folders already exist, if so, this should be the output results:

The file “SalesDB_Data01” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data02” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data03” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Log” has been modified in the system catalog. The new path will be used the next time the database is started.


What’s next? We must stop the SQL Engine Service and then manually move every database file to the new location we indicated in the code above. Finally, we have to start the SQL Engine service which will load the files from the new location. With this method you do not need to remove any setting mentioned before. This is extremely effective and there is no doubt that it will work. Having these files moved to the new location, the database will start without any problem. If not, you should make sure that the SQL Service account has Full Control permission on database files from the new location.
I hope this tip helps you to save time and it will ensure that your database will be available quickly. I will be pleased to answer any question you may have. Thanks for reading!.