One of a DBA from my client contacted me for a quick suggestion when I was working with them on a performance tuning exercise. I always ask for some time to explain the exact problem and behavior. As per the DBA, they are using 3rd party backup solutions to take backups and SQL backup are not happening. When they contacted 3rd party vendor they asked to run command VSSadmin List Writers and check if they have SqlServerWriter listed. Unfortunately, the component is missing from an output. He asked me the possible reasons. Since this was not a performance tuning related question and I promised that I would share whatever information I can find and write a blog post for that.
When I run it on one of my machine, I get below.
There are more Writers available which I am not showing in the image. The important point here is that the client was not seeing below, which I was able to see in my lab machine.
CHECKLIST
Based on my search on the internet, I found below possible causes.
- Make sure that the SQL Writer Service is installed.
If it’s not started, then start it. If it’s not available, then we need to install it from SQL installation media.
- Check Event logs and look for errors.
- Make sure that no database in the instance has trailing space in the name.
- Make sure service account for SQLWriter is able to connect to ALL instances on the machine.
WORKAROUND / SOLUTION
I was informed by my client that they had permission related issue and after changing the service account of SQL Server they could see the correct output and backups also started working.
Have you seen similar issue and found any solution other than what I listed earlier?
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
Problem:
We had a problem with our “Sql Server VSS Writer” (aka “SqlWriter”) on 5/29/2019.
In our case it appeared to be running (in the services list)
but we knew there was something wrong because:
* we were not seeing the typical “i/o freeze/thaw/backup” lines in our sql server errorlog
(we see these when our Actifio backup software does a backup,
and when our InMage disaster recovery software does a “check point”)
* when we ran “vssadmin list writers” it did not appear.
=========
Solution:
In our case the solution was to recycle the SqlWriter.
=========
Research:
This was the first time we had ever seen SqlWriter “hang”.
This is our sql and opsys info where it occurred:
Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) – 12.0.5214.6 (X64)
Jan 9 2018 15:03:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
We did find some strange errors in our Windows System Event Log
3 mins after the last time the Sql Writer was know to work correctly.
These services crashed and restarted successfully:
Windows Time service
Network Store Interface Service
Network List Service
Windows Font Cache Service
COM+ Event System service
Here is an example of one of those event log msgs
Log Name: System
Source: Service Control Manager
Date: 5/29/2019 3:22:07 PM
Event ID: 7031
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: xxxxxxxxxxxxxxxxxxx
Description:
The COM+ Event System service terminated unexpectedly. It has done this 1 time(s). The following corrective action will be taken in 1000 milliseconds: Restart the service.
-gary h