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

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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s