Troubleshooting VSS Writer issues

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)

VSS_writers

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.

VSS_writers_alias

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!

Advertisements

28 thoughts on “Troubleshooting VSS Writer issues

  1. i have a problem in backup relates to SQLSERVERwriter
    the vssadmin list writers returns with SQLSERVERwriter failed with status id 7 last error Time out

    Like

  2. Mate you are awesome!!! it was the alias in my instance there were 2 on the 32 bit and 1 on the 64bit. I have spent 3 days trying to fix this issue on and off after inheriting a box from another admin and discovering there has not been a backup for over 11 months.

    Like

  3. 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.

    >> SQLWriter was there but adding system account to sysadmin on SQL Server solved my problem. I was not able to backup SQL DB with SCDPM2012 before.
    THX!

    Like

  4. Could you please give an advice where to place the command
    “select ‘#’ + name +’#’ from sys.databases”?

    I have no idea where to launch this request (Management Studio?)

    Like

  5. Thanks so far. But to which database shall I logon? I’ve got some regular databases (backup exec, teamviewer , etc. and WSUS – beside \.pipemssql$microsoft##sseesqlquery)?

    Like

  6. Hello All, here’s my issue: I require to install SQL 2012 Express Local Db. The installation goes well and all files seems to have been copied properly, however when the service try to re-start I am left with the following error message: Service ‘SQL Server VSS Writer’ (SQL Writer) failed to start. Verify that you have sufficient privilges to start system services.
    I am an adminstrator for the user name / account is an administrator. I have delted all previous versions of SQL that I have and have tried to install separatly SQL express 2012, have also not been successful. Really stuck !
    Looking forward to your comment.

    Like

      • Hello, I have checked it was set as local system account. I have managed to install the SQL 2012 Express Local Db on three other machines today without any issue. All these machine are on the same domain ? What am I missing. This is crazy !. I am at the point of thinking rebuilding this machine but still hope for some resolution. Could there be anything to do with the hard disk controller ? (This machine uses an SSD ?).

        Like

      • Could you please follow this action plan:

        1. open command prompt with elevated privs.(run as administrator)
        2. type net start SQLWriter
        3. go to event viewer and share the error logs related to vsswriter with me

        You don’t need to rebuild this box. please share the error logs with me

        Like

  7. Hello, here is the folliwng response.
    Following steps.
    1) SQL 2012 Express Local Db error whilst starting service says “Service SQL Server VSS Writer (SQL Writer) failed to start. Verify that you have sufficient privileges to start system services
    2) Open command prompt and type net start SQLWriter
    command promp feedback is: the service is not responding to the control function
    3) Event viewer
    The SQL Server VSS Writer service failed to start due to the following error:
    The service did not respond to the start or control request in a timely fashion.
    – System

    – Provider
    [ Name] Service Control Manager
    [ Guid] {555908d1-a6d7-4695-8e1e-26931d2012f4}
    [ EventSourceName] Service Control Manager
    – EventID 7000
    [ Qualifiers] 49152
    Version 0
    Level 2
    Task 0
    Opcode 0
    Keywords 0x8080000000000000
    – TimeCreated
    [ SystemTime] 2013-04-18T07:43:44.797018400Z
    EventRecordID 288548
    Correlation
    – Execution
    [ ProcessID] 716
    [ ThreadID] 7092
    Channel System
    Computer mypcname.local
    Security
    – EventData
    param1 SQL Server VSS Writer
    param2 %%1053

    Look forward to feeback.
    Thanks

    Like

  8. In my case SQLwriter was there, but some backup (VSS does some sort of verify after the backup using this DB) had left a database with the name : “RollbackSnapshotTempDB{*}” where”*” is a SID or something like that. Removing that database made things work again 🙂

    Like

  9. I have this error:
    BackupVirtualDeviceFile::SendFileInfoBegin: failure on backup device ‘{A6FE5E6C-989C-413B-98C3-60620C1B5AD1}4’. Operating system error 995(failed to retrieve text for this error. Reason: 15105).

    On multiple hyper-v guests with different databases (including Microsoft WID) on different SQL Server platforms (Server 2012, Server 2008 R2). I have applied all SQL Server service packs.

    VSS just doesn’t work for SQL Server… actually it works sometimes but not always. It seems to be time based. If the VSS snapshot takes too long to execute (more than 10 seconds), the error occurs.

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s