Azure Site Recovery jobs fail on servers hosting SQL Server instances with AUTO_CLOSE DBs

Posted on by

This post is to help you if you are suffering from the following issue:

A VSS writer has rejected an event with error. The writer experienced a non-transient error. If the backup process is retried, the error is likely to reoccur. Changes that the writer made to the writer components while handling the event will not be available to the requester. Check the event log for related events from the application hosting the VSS writer.

Symptoms

Consider the following scenario:

  • You have a server that is running any version of Microsoft SQL Server.
  • This SQL Server instance hosts databases that have the AUTO-CLOSE option set.
  • You run a non-component VSS backup (for example, by using Azure Site Recovery (ASR) Agent) against volumes of this server that is hosting SQL Server database files.

In this situation, you notice that the VSS backup fails and triggers the following entry in the Application log.

This is how you fix this.

First, you need to download and install 3 components in the following order please.

  • Visual Studio c++ redistributable 2017
  • ODBC SQL Server 17 driver
  • SQLCMD install

We are trying to connect to the database – Windows Internal Database (WID).

mdfs1

Lauch SQLCMD as adminstrator.

Run: sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query -E 

Here I run SELECT @@VERSION before fixing the Auto Close settings.

wid

Then run:

ALTER DATABASE <myDatabase> set auto_close off
go
ALTER DATABASE <myDatabase> set auto_close off
go

 

 

1 thought on “Azure Site Recovery jobs fail on servers hosting SQL Server instances with AUTO_CLOSE DBs”

Leave a Reply