windows server 2008 backup fails - sqlserverwriter failed non-retryable error

Dear experts,

I have an SBS2008 box.  Everything running fine for over a year. Then I installed Windows server 2008 SP2 and Exchange 2007 SP3 and also any other Microsoft  updates which were pending after that. The server has SQL server 2005 express installed with some SBS components (monitoring and the like) and also another instance containing a database for an application.

Since the updates, the windows server backup fails (or fails to start would be more accurate). There are several entries in the event log and i have traced it back to a problem with the vss sqlserverwriter.

Using the "vssadmin list writers" command i can see that the sqlserverwriter fails as shown below:

********************************* start ******************************************

Writer name: 'SqlServerWriter'
   Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
   Writer Instance Id: {cc18327c-615f-429b-ad99-16cdc1caa8ea}
   State: [8] Failed
   Last error: Non-retryable error

********************************* end *******************************************

also I get (amongst others)  the following event logged:

********************************* start ******************************************

The description for Event ID 24583 from source SQLWRITER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

ICommandText::Execute
0x80040e14
SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Native Client
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 924
Error state: 1, Severity: 14
Source: Microsoft SQL Native Client
Error message: Database 'ShareWebDb' is already open and can only have one user at a time.


the message resource is present but the message is not found in the string/message table

********************************* end *******************************************


When I stop the "VSS SQL server writer" service the backup runs successfully.
When I start it again the backup fails.

I have investigated and discovered a lot of people with the same issue. Workarounds galore involving scripts to stop the vss writer service etc, but i would prefer to resolve the issue fully to ensure that the sql databases are backed up correctly.

MS KB http://support.microsoft.com/kb/919023 offers a decent proposal and seems to accurately describe the issue with the vss writer.  It suggests that the issue is with authentication of the vss service and the SQL database.  

Quote:
Because of strict security requirements, SQL Server installations may have removed the BUILTIN\Administrators group from the SQL Server logins. If this issue occurs, you have to make sure that the NT AUTHORITY\SYSTEM account is granted access to SQL Server. To do this, you must add it as an explicit login. Additionally, because of the types of operations that the writer must perform, we recommend that you do not remove the NT AUTHORITY\SYSTEM login from the sysadmin server role.

I'm guessing that an update has modified the authentication for the vss writer in some way.

So, I have three questions:

1. How does disabling the  "VSS SQL server writer" service impact on a successful backup of the SQL databases?

2. Can the sqlserverwriter component be re-installed and will this even help?

3. How can I "make sure that the NT AUTHORITY\SYSTEM account is granted access to SQL Server" as per MS KB http://support.microsoft.com/kb/919023 ?  I don't have much experience  with SQL management and would need step-by-step or as close to it as possible.

Thanks in advance.
CruthinAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
bhanukir7Connect With a Mentor Commented:
Hi cruthin,

the SSEE database in a SBS server is run to maintain WSUS and also the monitoring information and sharepoint.

This is a internal database and this is very important for the SBS server monitoring. You can certainly prune the database

check this blog entry

http://msmvps.com/blogs/bradley/archive/2006/05/25/97044.aspx

for more info on SBS SSEE DB check this technet article

http://technet.microsoft.com/en-us/library/cc794697%28WS.10%29.aspx

regards
bhanu
0
 
CruthinAuthor Commented:
Update:

I have narrowed it down further to an SQL instance. The Service is called "windows internal database (MICROSOFT##SSEE)"   When i stop this service and perform a backup with the  "VSS SQL server writer" service started, the backup completes perfectly.

As I understand it, this database is used by WSUS.  

Any thoughts?
0
 
CruthinAuthor Commented:
Thanks for your interest.  Would pruning the databse help resolve my specific issue though? It would appear that the issue lies with either connection or authenticaiton between the VSS sql server writer and the ssee database.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bhanukir7Commented:
ok let me give you a little insight of VSS writer errors from my backup support experience....

when a backup runs it first tries to run a list of vss writers and it waits for the query to return the list of VSS writers and fails if the same does not return a stable state for all....

though the VSS writers are very flaky...applying the latest hotfixes and patches would address this and as you have mentoined pruning the DB would certainly help....

all the best

a typical output of the command vssadmin list writers is given below : (btw this is a win XP machine output)

C:\Documents and Settings\mycomputer>vssadmin list writers
vssadmin 1.0 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001 Microsoft Corp.

Writer name: 'MSDEWriter'
Writer Id: {f8544ac1-0611-4fa5-b04b-f7ee00b03277}
Writer Instance Id: {f122c543-44e3-4a18-b0da-0a124a0f4d5e}
State: [1] Stable

Writer name: 'WMI Writer'
Writer Id: {a6ad56c2-b509-4e6c-bb19-49d8f43532f0}
Writer Instance Id: {7c836e5a-0595-468a-8c8a-727d49fbf06d}
State: [1] Stable

Writer name: 'Microsoft Writer (Service State)'
Writer Id: {e38c2e3c-d4fb-4f4d-9550-fcafda8aae9a}
Writer Instance Id: {213c1219-9458-4807-9065-f3a9efe7b0ee}
State: [1] Stable

Writer name: 'Microsoft Writer (Bootable State)'
Writer Id: {f2436e37-09f5-41af-9b2a-4ca2435dbfd5}
Writer Instance Id: {748dbf98-cb14-447d-a9dc-128fb6d302da}
State: [1] Stable
0
 
CruthinAuthor Commented:
thanks bhanukir7.  I will prune the database and report back.
0
 
CruthinAuthor Commented:
The article at http://msmvps.com/blogs/bradley/archive/2006/05/25/97044.aspx it for SBS2003.  I have a SBS2008 box.  

Do you have any details on doing the job for SBS2008?

0
 
bhanukir7Commented:
hi Cruthin,

We will first try to fix the issues with VSS writers and then follow the links given in the later half of the post to fix the SBS monitoring database as this is bit lenghty and time taking than fixing the VSS writers issues.

The MS SQL VSS KB article is very exhaustive which might have made you wonder what all needs to be done.

The simplest part to do is download SQL managment studio if you have not already installed it.

After installing the SQL mgmt studio......


 Click "Start"->"All Programs"->"Microsoft SQL Server 2005"->"SQL Server
Management Studio" to run this tool


0
 
bhanukir7Commented:
i was trying to add the image and hit the submit button anyhow...check this link... dont get scared by the length of the link.


http://msmvps.com/blogs/bradley/archive/2010/07/10/i-have-been-getting-the-warning-that-my-sharepoint-config-file-is-greater-than-a-gig.aspx

once the SQL managment studio opens type the server name column "\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query"

once you are connected.....

click on the database security and add the permissions to the "NT AUTHORITY\SYSTEM" under security\logins for this instance.


now coming to maintaining the #SSEE database in SBS 2008 this is a lenghty process and needs lot of free disk space...another long link

http://blogs.technet.com/sbs/archive/2009/07/14/sbs-2008-console-may-take-too-long-to-display-alerts-and-security-statuses-display-not-available-or-crash.aspx


regards
bhanu
0
 
CruthinAuthor Commented:
That's great. does look a bit scary though!  

I'll not get a chance to perform these tasks until weekend, but I'll post back when complete.

thanks again.
0
 
CruthinAuthor Commented:
bhanu,

I still havent applied your suggestion yet, but hope to do so next week, then i'll let you know how i got on.

Thanks
0
 
CruthinAuthor Commented:
bhanu,

you wrote:

" once the SQL managment studio opens type the server name column "\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query"

once you are connected.....

click on the database security and add the permissions to the "NT AUTHORITY\SYSTEM" under security\logins for this instance."

The user "NT AUTHORITY\SYSTEM"  already exists in the security\logins.

Before I ran the updateSBSMonitoring script, the SBSMonitoring.mdf file was 188Mb and the  SBSMonitoring_log.LDF was 22Mb.   After the script was run, the mdf was 188Mb and the ldf grew to 625Mb.

Also as per the first link, when I try to get the properties of the sharepoint_config database, I get the following message:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)

------------------------------

Property Owner is not available for Database '[SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
 
GrieprCommented:
This may seem very basic and forgive me if you've already looked at this, but have you checked the space on your C: Drive?  I've seen this error when the drive is full.
0
 
CruthinAuthor Commented:
Yeah - there's loads free.
0
 
bhanukir7Commented:
hi cruthin,

on your query about the SQL ldf file, run a backup to purge the ldf file that should reduce the size.

bhanu
0
All Courses

From novice to tech pro — start learning today.