Non-component VSS backups such as Azure Site Recovery jobs fail on servers hosting SQL Server instances with AUTO_CLOSE DBs

Applies to: SQL Server 2017 on WindowsSQL Server 2016SQL Server 2014

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:

A VSS writer has rejected an event with error 0x800423f4, 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.

Operation:

   PostSnapshot Event

Context:

   Execution Context: Writer

   Writer Class Id: {ID}

   Writer Name: SqlServerWriter

   Writer Instance Name: Microsoft SQL Server 2012:SQLWriter

   Writer Instance ID: {ID}

   Command Line: ""C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe""

   Process ID: xxx"

Cause


This problem occurs because SQL Server SQLWriter currently doesn't handle AUTO-CLOSE databases correctly in non-component mode VSS backup requests.

Workaround


As a short-term mitigation, we recommend that you disable the AUTO-CLOSE option on all databases of all SQL Server instances that are hosted on servers that receive non-component VSS backups. Typically, Azure virtual machines that run SQL Server are affected because ASR Agent runs such non-component backups.

More information


  • By default, the AUTO_CLOSE property is set to OFF in SQL Server except for SQL Express instances. If you're confident that you didn’t enable this setting manually on servers that might be affected by this problem, investigate any SQL Server Express instances that may have been silently installed as components of other applications.
  • To get a list of databases that have AUTO_CLOSE mode enabled, run the following query against a given SQL Server instance:
select name,database_id,is_auto_close_on from sys.databases where is_auto_close_on=1
  • To change the setting, refer to the AUTO_CLOSE section of ALTER DATABASE SET Options in online documentation for TSQL.        
    • To toggle this option to OFF, run the following command in the default client "sqlcmd.exe" (for example, for the "My Database" database):
      • alter database <myDatabase> set auto_close OFF
    • The change takes effect immediately. To revert this change, run the following command:
      • alter database <myDatabase> set auto_close ON
  • If you prefer a GUI method, use "Database Properties > Options" in SQL Server Management Studio.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References


Learn about the terminology that Microsoft uses to describe software updates.