Several customers use log shipping for disaster recovery across a remote data center. If you are reading this blog, I would assume that you already know SQL Server log shipping If you don’t, then please go through online resources to read the basics. Here is an interesting fact which I learned recently about the WRK file in log shipping. I know some of these are around as theory, but there is no concrete way to understand the nuances of what these files actually mean.
Even if you have worked with log shipping for many years, there are chances that you might not have noticed this file (at least I didn’t notice this file till today). This is one such thing that is hidden deep into the product that unless you are in the mode of getting to know it, it never gets surfaced. At least that has been my understanding while I was trying to read up on this.
Here is an animation of a folder located on secondary server. This is the folder where transaction log backups are copied from the backup location via the SQL Agent “copy” job in secondary server.
As you would notice in the animation, a file foo_20160421004020.wrk created and shortly extension of this was automatically changed as trn. Based on the observation, the files are named as wrk during the copy operation. One it is completely copied, wrk file is renamed as trn extension. If you are trying to reproduce the behavior, make sure you do a big transaction because you would notice it only when file size of transaction log backup is at least few MB. You can think of this as a safety net so that the transaction log “restore” job is not picking the file which is “in-process”.
Did you know this earlier? Are there any other interesting facts you want to share?
Reference: Pinal Dave (https://blog.sqlauthority.com)