SQL SERVER – SqlServerWriter Missing in vssadmin List Writers Command

SQL
No Comments

There are multiple ways by which we can take a backup of the SQL Server database. One of the methods used by 3rd party providers is using VSS (Volume Shadow Copy Service). One of my clients contacted me that 3rd party backup is failing. After troubleshooting they found that when they run vssadmin list writers command from the command prompt, they are not seeing SqlServerWriter. When they compared with a working machine, they found that the writer is listed. I faced this error with one of my clients of Comprehensive Database Performance Health Check.

SQL SERVER - SqlServerWriter Missing in vssadmin List Writers Command SqlServerWriter-800x182

Here is the command to search for SQLSerevrWriter in the output of vssasdmin list writers.

vssadmin list writers | findstr /I SQLServerWriter

Here is the output on the working machine.

SQL SERVER - SqlServerWriter Missing in vssadmin List Writers Command sqlwriter-missing-01

SqlServerWriter

On the non-working machine, we are seeing no output.

SQL SERVER - SqlServerWriter Missing in vssadmin List Writers Command sqlwriter-missing-02

WORKAROUND/SOLUTION – SqlServerWriter

There might be multiple reasons for SqlServerWriter missing in the output. One of the reasons is the whitespace at the end of the database name.

The easiest way to find if you are running into this is to run the below query.

SELECT name
	,LEN(LTRIM(RTRIM(name)) + '%') 'Length1'
	,LEN(name + '%') 'Length2'
FROM sys.databases
WHERE LEN(LTRIM(RTRIM(name)) + '%') <> LEN(name + '%')

If you see any rows as output, then it means that there is whitespace at the end of the database name. You must fix it to move forward. Note that this needs to be checked on ALL instances running on the machine.

Another issue could be permitted for the SQL Server Writer service account. As per documentation, it must be a Sysadmin in ALL SQL Server instances running on the machine.

Here are some really good blog posts which you can read about parameter sniffing and how you can avoid the problem related to it.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – SQL Server Agent Missing in SQL Server Configuration Manager
Next Post
SQL SERVER – Enable Additional Logging for SQL Server Agent

Related Posts

Leave a Reply

Menu