Sql writer ошибка

Проблемы

Служба модуля записи SQL не обрабатывает моментальные снимки базы данных (например, «создать базу данных DataSnapName AS Snapshot») в пути кода резервной копии некомпонентных данных в SQL Server 2016, 2017 или 2019. При запуске РЕЗЕРВной копии базы данных в ходе архивации службы теневого копирования тома может возникнуть следующая запись журнала ошибок.

DateTime Архивация резервной копии не удалось завершить DataSnapNameрезервной копии базы данных. Просмотрите журнал приложения резервного копирования для получения подробных сообщений.

DateTime Ошибка резервного копирования: 3041, уровень серьезности: 16, состояние: 1.

DateTime Архивация резервной копии не удалось выполнить команду DatabaseNameдля базы данных резервного копирования. Просмотрите журнал приложения резервного копирования для получения подробных сообщений.

Дата и время SPID # # ошибка: 18210, уровень серьезности: 16, состояние: 1.

DateTime SPID # # BackupVirtualDeviceFile::P reparetofreeze: сбой на устройстве резервного копирования «{DeviceToken} 1″. Ошибка операционной системы 995 (операция ввода-вывода прервана из-за выхода из потока или запроса приложения).

Примечание. Указанные выше ошибки являются общими и могут
встречаются во многих сценариях. Ошибка «имя базы»
имеет силу только из-за того, что в DataSnapName возникла ошибка. Текущее исправление предназначено только для
ситуация, когда возникает ошибка при наличии базы данных SQL Server
Снимок диска, выбранный для резервной копии VSS.

В то же время журнал событий приложений Windows может содержать запись, которая похожа на приведенную ниже.

Ошибка в time SQLWRITER 24583 None

Ошибка Sqllib: ошибка OLEDB при вызове ICommandText:: Execute. HR =
0x80040e14. SQLSTATE: 42000, Native Error: 3013

Состояние ошибки: 1, степень серьезности: 16

Источник: Microsoft SQL Server Native Client 11,0

Сообщение об ошибке: «резервное копирование базы данных» аварийно завершается.

SQLSTATE: 42000, Native Error: 3002

Состояние ошибки: 1, степень серьезности: 16

Источник: Microsoft SQL Server Native Client 11,0

Сообщение об ошибке: не удается выполнить резервное копирование или восстановление моментального снимка базы данных.

Статус

Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».

Решение

Эта проблема исправлена в перечисленных ниже накопительных обновлениях для SQL Server.

• Накопительное обновление 1 для SQL Server 2019

• Накопительное обновление 17 для SQL Server 2017

• Накопительное обновление 10 для SQL Server 2016 с пакетом обновления 2 (SP2)

Сведения о накопительных обновлениях для SQL Server.

Все новые накопительные обновления для SQL Server содержат все исправления и все исправления для системы безопасности, которые были включены в предыдущий накопительный пакет обновления. Ознакомьтесь с самыми последними накопительными обновлениями для SQL Server.

• Последнее накопительное обновление для SQL Server 2019

• Последнее накопительное обновление для SQL Server 2017

• Последнее накопительное обновление для SQL Server 2016

Ссылки

Ознакомьтесь с терминологией , которую корпорация Майкрософт использует для описания обновлений программного обеспечения.

Are you looking for a way to resolve the SQLWriter Service is not running on the target device Our SQLServer Support team is here to lend a hand with your queries and issues.

SQLWriter Service is not running on the target device

Some of our customers have been experiencing the following error when they attempt to back up MS SQL.

SQLWriter Service is not running on the target device

In some scenarios, this error also prevents vssadmin list writers command from returning SQLServer Writer.

This service is responsible for creating and managing snapshots of SQL Server databases, which are used for backup and restore operations.

Troubleshooting Tips

Fortunately, our experts have put together a few troubleshooting tips to help you out. Before we begin, we have to restart SQL Server VSS Writer. This is done by heading to services.msc and locating SQL server VS Writer.

Then, we have to click Restart or click Stop followed by Start.

After this, our experts recommend listing writers with the following command:

vssadmin list writers

If SQLWriter Service is missing in the output, it is time to use the following troubleshooting tips:

System State misconfiguration

In some scenarios, the error may pop up due to a system state misconfiguration. This involves the error ((HRESULT)0x800423F4L) (SetCurrentFailure) appearing in the EventViewer log.

This is due to issues between the SQL writer and the SharePoint VSS writer. We can resolve the error by removing the GUID entry from the following registry key:

HKEY_LOCAL_MACHINE/ SOFTWARE/ MICROSOFT/ MICROSOFT SQL SERVER/ SHAREPOINT/SQLSERVERAGENT

Furthermore, the error entry is tracked in the registry in case the file Sqlvdi.dll on the system has an issue.

SQL database name contains spaces:

If the above tip did not do the trick, it is time to check if the SQL database name contains spaces.

1. First, we have to open MS SQL Management Studio and then right-click on any database and choose Run query item.
2. Next, run the following query:

   select '#' + name +'#' from sys.databases
3. In case any of the names have a space within the name, we have to remove it.

SQL Server VSS Writer has insufficient privileges:

1. First, we have to check the SQL server security attributes of the chosen user as the service logon for the SQL writer and verify if they have sysadmin privileges.
2. In case the privileges are not sufficient, we have to add them.

Additionally, the error may also appear if a 32-bit SQL Server VSS Writer is running on a 64-bit system. We can check if this is the reason behind the error by verifying it via the task manager.

Let us know in the comments which one of the above tips helped you resolve the error.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To sum up, our Support Techs demonstrated how to resolve the “SQLWriter Service is not running on the target device” error.

In this blog I will discuss about various issues you can come across with SQL VSS Writer, while working with third party backup tools e.g. IBM Tivoli , Quest Litespeed etc.

I have worked on many such issues and I will try to cover most of the scenarios which I have troubleshot. TBH, I have seen most of the times – just doing basics resolves the issue. There are very few extreme scenarios when we take VSS writer traces and troubleshoot

Lets get started, I will start with a basic scenario

1. we run Vssadmin list writers command and sqlserverwriter is  missing and

     Also, there is no error message in Application event logs and

     Also, SQLserver VSS writer service is running

Here are the steps you need to follow to resolve such issues.

First of all we need to check if there are any spaces in the databases names. please run the query :

select ‘#’ + name +’#’ from sys.databases

if you notice any space in database names e.g. #test # , we need to make sure we remove the space from the database name.

Please follow this article to rename the database name : – http://msdn.microsoft.com/en-us/library/ms345378.aspx

once you remove the space in the database name, then issue vssadmin list writers command and check if the SQLwriter is there or not

If still SQLwriter is not there then:

please check the service logon which is mostly Nt authority/system or any user which you have mentioned as service logon, is added in SQL server as sysadmin privileges or not. If it’s not there then please make it as syadmin. Run the VSSadmin list writers command.

Most of the times the above 2 steps resolves the issues.

2.  We run VSSadmin list writers and SqlServerWriter fails:

First thing to check is open Windows application event logs and check for the errors. Here is the article which describes all about how to handle the connectivity issues: http://support.microsoft.com/kb/919023

Message 1
Event Type: Error
Event Source: SQLWRITER
Event Category: None
Event ID: 24583
Date: 4/30/2006
Time: 11:38:44 AM
User: N/A
Computer: ComputerName
Description:
Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 28000, Native Error: 18456
Error state: 1, Severity: 14
Source: Microsoft SQL Native Client
Error message: Login failed for user ‘NT AUTHORITYSYSTEM’.
DBPROP_INIT_DATASOURCE: ComputerName
DBPROP_INIT_CATALOG: master
DBPROP_AUTH_INTEGRATED: SSPI

Message 2

Event Type: Error
Event Source: SQLWRITER
Event Category: None
Event ID: 24583
Date: 4/30/2006
Time: 11:38:44 AM
User: N/A
Computer: ComputerName
Description:
Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 28000, Native Error: 18456
Error state: 1, Severity: 14
Source: Microsoft SQL Native Client
Error message: Login failed for user ‘NT AUTHORITYSYSTEM’.
DBPROP_INIT_DATASOURCE: ComputerName
DBPROP_INIT_CATALOG: master
DBPROP_AUTH_INTEGRATED: SSPI

Message 3

Event Type: Error
Event Source: VSS
Event Category: None
Event ID: 6013
Date: 4/30/2006
Time: 11:38:44 AM
User: N/A
Computer: ComputerName
Description:
Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 42000, Native Error: 18456
Error state: 1, Severity: 14
Source: Microsoft OLE DB Provider for SQL Server
Error message: Login failed for user ‘NT AUTHORITYSYSTEM’.

In essence, here are the points you need to make sure of:

1. TCP/IP,Shared Memory and Named Pipe protocols should be enabled. (for 32 bit ,64 bit SQL native clients and Network configuration)

2. Logon account for SQL Server VSS Writer Service ( which is NT AuthoritySystem , most of the times) should have sysadmin privileges in SQL server

3. There should not be any alias in SQL server configuration manager. If you see something highlighted in the right hand side of the screenshot.

You will have to remove that and test the VSSadmin list writers command again. If the SqlServerWriter is now successful then you have found the culprit.

Another exceptional issue, which I worked on was :-  when we ran VSSadmin list writers command , SqlServerWriter fails. After we checked the application logs , the error was:

Error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80004005. SQLSTATE: HYT00, Native Error: 0 Source: Microsoft SQL Native Client Error message: Login timeout expired SQLSTATE: 08001, Native Error: 2 Source: Microsoft SQL Native Client Error message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. SQLSTATE: 08001, Native Error: 2 Error state: 1, Severity: 16 Source: Microsoft SQL Native Client Error message: Named Pipes Provider: Could not open a connection to SQL Server [2]. 

DBPROP_INIT_DATASOURCE: Server Name à Default instance name.
DBPROP_INIT_CATALOG: master
DBPROP_AUTH_INTEGRATED: SSPI

To check how the issue was resolved please check :- http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/28/backup-software-fails-to-take-system-state-backup-if-sql-server-vss-writer-service-is-running.aspx

==> When we have more than 127 databases then also, we can face the issue. The error in that case may be something like this:

Log Name:      Application Source:        SQLWRITER Date:          19/9/2012 1:29:16 μμ Event ID:      24583 Task Category: None Level:         Error Keywords:      Classic User:          N/A Computer:      SQL.domain.local Description: Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 10.0 Error message: BACKUP DATABASE is terminating abnormally. SQLSTATE: 42000, Native Error: 3202 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 10.0 Error message: Write on “{E3FE4354-2B95-4C2B-85A7-639F4E3F7B0E}29” failed: 995(failed to retrieve text for this error. Reason: 15105)

Here is a very good article which explains about the issue which you may face with large number of databases :- http://blogs.msdn.com/b/psssql/archive/2009/11/13/how-it-works-how-many-databases-can-be-backed-up-simultaneously.aspx

Here is the solution for this issue:- http://support.microsoft.com/kb/943471

3. Many times, I have heard from the customer that when they shut down the SQL server VSS Writer service, the issue gets resolved.

After probing further about what files they are backing up, I got to know that they are backing up some OS files except SQL server database files.  Well, this is strange. Isn’t it?

I tried to find out the reason and here is what I got (http://support.microsoft.com/kb/919023):

You might be wondering why VSS framework components would need to connect to SQL Server when the components are only performing a backup of the volume. During the initial phases of snapshot creation, the configured default writer makes a connection to the instances of SQL Server on the particular server. One of the first phases of a snapshot creation process is “Backup Initialization.” During this phase, the backup application (requestor) performs the following actions to make sure that all the components in the snapshot creation process are ready:

• The backup application binds to the IVssBackupComponentsinterface.
• The backup application initializes the IVssBackupComponentsinterface.
• The backup application calls the IVssGatherWriterMetadata API to perform metadata enumeration.

The VSS framework then instructs all writers to gather metadata. This includes a default writer that is included with SQL Server. It could be either MSDEWriter or SqlServerWriter based on the server’s current settings. This default writer for SQL Server connects to all instances of SQL Server that are started on the local system, obtains the required information about the databases on the instance of SQL Server, and then creates the metadata document. The metadata document is then returned to the backup application.

The failure in this scenario is because of – Logon account for SQL Server VSS Writer Service ( which is NT AuthoritySystem , most of the times) doesn’t have sysadmin privileges in SQL server.

If you fix the permission, then you won’t need to stop SQL VSS Writer Service.

4. Some windows bug: I worked on a scenario where ntbackup used to fail while SQL VSSwriter service was in running state. After the failure of backup, vssadmin list writers command on command prompt used to throw error:
Writer name: sqlserverWriter Writer Id: {a65faa63—5ea8—4ehc—9dbd—ac4db26912a) Writer Instance Id: {d4343f5O—672e—4a11—b2f9—333f9ebff3)
State: [8]
Failed Last error: Inconsistent shadow copy

This command used to return successful results prior to the backup failure. The issue was found to be due to bug of windows mentioned in the article : – http://support.microsoft.com/kb/2457458

If after following the above steps, still the issue persists then the issue is falling in the category of so called “Extreme Scenario”

First thing, I would do is to run a very good tool names backup simulator. This tool was written by my colleague Amit Banerjee. This article describes about how it works: –http://troubleshootingsql.com/2011/06/18/sql-server-backup-simulator-v2-available-now/

Backup simulator tool will help to narrow down if the issue is with SQLVDI or the third party tool which we are using. This tool validates the SQLVDI infrastructure and will take a small backup. If it succeeds then the issue will probably be with the third party tool.

Here are few errors which you will see if there is any issue with SQLVDI :

Error message 1
2007-06-18 11:21:00.83 spid820 BackupVirtualDeviceFile::ClearError: failure on backup device ‘VDI_ DeviceID ‘. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

Error message 2
2007-06-18 11:21:00.83 spid820 Error: 18210, Severity: 16, State: 1.
2007-06-18 11:21:00.83 spid820 BackupMedium::ReportIoError: write failure on backup
device ‘VDI_ DeviceID ‘. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.)

Error message 3
2007-06-18 11:21:00.87 spid820 Error: 18210, Severity: 16, State: 1.
2007-06-18 11:21:00.87 spid820 BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device ‘VDI_ DeviceID . Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.)

For further information on this, please check this blog: – http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/28/is-sqlvdi-dll-functioning-properly.aspx

References :-
How SQL VDI works: – http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/28/informational-shedding-light-on-vss-vdi-backups-in-sql-server.aspx

Backup Simulator: – http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-backup-simulator.aspx

VSS connectivity Issues: – http://support.microsoft.com/kb/919023

Please feel free to leave comments in case you have any questions.

HTH!

Symptoms

You have installed Acronis Cyber Backup Agent for SQL and created a backup plan for a SQL instance.

Backup task fails with this error: The backup of this SQL Server has failed because of a VSS snapshot failure: No SQL writers are found in the operating system

Activity details (click to expand)

OR

Backup fails with:

The backup has failed because ‘SqlServerWriter’ has failed during snapshot creation.

And in Windows Application Event log, you notice this error from SQLWRITER:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0 Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3265
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0 Error message: The login has insufficient authority. Membership of the sysadmin role is required to use VIRTUAL_DEVICE with BACKUP or RESTORE.

Cause

Possible causes for this error issue include:

• The SQL writer service is disabled
• The SQL writer service fails to start correctly
• The service account for the SQL writer service is missing the necessary privileges
• Issue with SQL instance or database
• There are spaces in database name
• SQL VSS writer is not present on the system

Troubleshooting

The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework, and its proper functioning is required in order for Acronis Cyber Backup to work:

1)  Hit Win+R and type services.msc. In Services window, find SQL Writer Service. If it is not started, right-click on it and select Start.

2) Make sure that SQL Writer Service runs under Local System account

3) If SQL Writer Service fails to start or crashes, investigate the issue with the service. Check the error message and Windows Application Event logs for possible causes of failure.

4) Check that the user account is added to SQL Server security logins and that it has the «sysadmin» role for the SQL instance:

1. Using SQL Management Studio, connect to the SQL instance.
2. Go to Security -> Logins.
3. Open account properties and switch to Server Roles:
4. Mark the sysadmin check box:

5) On a domain controller, for example an SBS server, you might see that SqlServerWriter is not listed in the output of the vssadmin list writers command. In this case, change SQL VSS Writer Service user from Local System to domain administrator.

6) Check if there are spaces in database names:

• Open MS SQL Management Studio, right-click on any database and select Run query 
• Run the following query: select ‘#’ + name +’#’  from sys.databases where name like ‘% %’
• If you notice any space in database names e.g. #database01  #, rename the database to exclude the space: see MS documentation for instructions.

7) Check the state of SQL VSS writer:

• Open an elevated command prompt: open the start menu and type CMD in the search bar, then right-click the command prompt and select Run as Administrator.
• Execute the following  command to check VSS writers state:

       vssadmin list writers

• Check that SQL VSS writer is present in the list and its state is Stable. Acronis software relies on VSS to perform backup operations and the correct functioning of SQL VSS writer is a prerequisite for a successful SQL backup.  

  If SQL VSS writer is not present on the system, the problem should be addressed to Microsoft and/or researched using their support resources. 

More information

If the above solution does not help, use Acronis VSS Doctor for further troubleshooting.

For assistance, please contact Acronis Support.

I encountered this problem during installation of the SQL Server 2019 express edition. The installation failure suggested me to install Microsoft Visual C++ 2017 Redistributable (x64). But, even after installing the 2017 redistributable the problem persisted. However, the redistributable provided by the link in this answer is titled «Microsoft Visual C++ 2015-2019 Redistributable (x64)» actually solved my problem. The latter’s version number is 14.28.29325 whereas that of the former (2017) is 14.16.27033.

Feb 20, 2021 at 10:50