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.
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.
On the non-working machine, we are seeing no output.
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.
- SQL SERVER – Parameter Sniffing Simplest Example
- SQL SERVER – Parameter Sniffing and Local Variable in SP
- SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
- SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
- SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
- Performance and Recompiling Query – Summary
Reference: Pinal Dave (https://blog.sqlauthority.com)