Database named "RollbackSnapshotTempDB" left after System Recovery 2013 backup

[ammacdo]

We have a Windows Server 2008 R2 server with SQL Server 2008 R2 x64 installed. We have been using Backup Exec 2010 to perform Full and Transaction log backups of the databases in our SQL server, and System Recovery 2010 to backup the entire server. A couple weeks ago we upgraded System Recovery 2010 to 2013 and we noticed our Backup Exec 2010 jobs started failing. After investigating we discovered several databases named "RollbackSnapshotTempDB" were piling up. We tried to right-click the DB and go to properties but received an error message.

The SQL logs showed a lot of errors related to those "RollbackSnapshotTempDB" databases, such as:

Date  7/2/2013 6:07:37 AM
Log  SQL Server (Current - 7/16/2013 8:38:00 AM)

Source  spid400

Message
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000053fc2000 in file '\\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy11\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\XXXXXX.MDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

And:

Date  7/2/2013 6:07:37 AM
Log  SQL Server (Current - 7/16/2013 8:38:00 AM)

Source  spid400

Message
Error: 823, Severity: 24, State: 2.

If I go into the SQL Server Management Studio and and try to delete the "RollbackSnapshotTempDB" databse I receive an error that says "Cannot drop the database because it is being used for replication". However if I take the "RollbackSnapshotTempDB" database offline then I can delete it, and the next Backup Exec 2010 SQL backup job will complete successfully.

These errors seem to indicate a file system problem, however a CHKDSK shows no problems. We have rebooted the server as well with no change.

I have googled this issue until I'm blue in the face and have not found any solutions. Does anyone have any ideas?

Here are more details on the server:

Virtual Server running on ESXi 5.1 Update 1

Physical host is HP Proliant DL380 G6

Storage provided by HP LeftHand iSCSI SAN

Server 2008 R2 with all Windows Updates applied

SQL Server 2008 R2 with all Windows Updates applied

Server has two vCPU's and 12GB of RAM

C: drive has 21GB free

D: drive has 88GB free (D: is where all the SQL data files are stored)

[SER-VIS_s_r_l_]

Hi, we have the same problem here.

Windows 2008R2 SP1 Standard, with SQL Server 2008 Standard with SP3:

Microsoft SQL Server Management Studio      10.0.5512.0
Microsoft Analysis Services Client Tools      10.0.5500.0
Microsoft Data Access Components (MDAC)      6.1.7601.17514
Microsoft MSXML      3.0 4.0 6.0
Microsoft Internet Explorer      9.10.9200.16635
Microsoft .NET Framework      2.0.50727.5472
Operating System      6.1.7601

Now we have BackupExec 2010 full updated to perform SQL backup and System Recovery 2013 (no VSS in use to void conflicts with BackupExec) for full server backup. "SystemRecovery 2013" jobs runs fine, with no errors. SQL creates a "RollbackSnapshotTempDB" databases for each run of BackupExec even and only for the same database. Backup's jobs doesn't overlap each other.

 To delete this db, you can take it offline before deleting.

We done a complete full database consistency check (DBCC CHECKDB) on that db with no errors but problem persists.

We done a SQL backup of that db, deleted it and restored it  but problem persists.

We disabled SystemRecovery 2013 schedules and the problem disappear, so this software should be the cause, maybe a VSS interference. With SystemRecovery2010 we had no problems.

[criley]

You may be seeing this issue now, with SSR 2013, because we now use Microsoft VSS to take a snapshot.

With earlier versions of SSR/BESR, we had our own VSS provider to do this work.

I'm not saying this is a Microsoft issue but I would recommend one of you, if you can, open a case with Microsoft support so we can get their input on this.

[Andreas_Horlach]

Questions After the BE job fails, can you open an elevated command prompt and type VSSADMIN LIST WRITERS ? Are they all STABLE? Do you have SHADOW COPIES enabled on any volume at all? Does it make any difference if the option PERFORM FULL VSS BACKUP is checked\unchecked? (If you do not select this option, SSR 2013 backups still occur on the VSS storage. However, VSS does not automatically truncate the transaction logs following a backup.)

[manishkrsingh]

While we are duplicating the problem, its not clear from the post that SSR 2013 SP1 is installed. Could you confirm it ? We have a VSS fix gone into this. If not, please install it and let us know if problem persists.

[SER-VIS_s_r_l_]

 LiveUpdate reported that no updates are available, I presume that SP1 is installed (version 11.0.1.47662).

 I've enabled the option "PERFORM FULL VSS BACKUP" in SSR 2013 and I'll run a job in this way in a few minutes but I think that this setting will cause the job will to fail with the following error "The Backup Exec SQL Agent was not used to create the last full, differential, or log backup of this database. You must use the SQL Agent to run a full backup before you can run a differential backup or transaction log backup."
 

[criley]

Yes, that is SP1.

The 'Perform Full VSS Backup' option only applies to Exchange backups - it wont have any effect on SQL backups..

[SER-VIS_s_r_l_]

Done.

After performing a full VSS backup, SSR 2013 leave again a RollbackSnapshotTempDB. Following, the output of "VSSADMIN LIST WRITERS" (attached). Are all "stable" except for the following:

 Writer name: 'SqlServerWriter'
   Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
   Writer Instance Id: {f2902d58-a841-4700-8cc2-03c61207060a}
   State: [11] Failed
   Last error: Non-retryable error

In Windows EventLog we have the following errors:

1_ (EventID 24583, Source SQLWRITER):

Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3724
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 10.0
Error message: Cannot drop the database 'RollbackSnapshotTempDB{A80DC5F4-8004-4718-BCBC-AB66A9C66F08}' because it is being used for replication.
 

2_ (EventID 8229, Source VSS):

A VSS writer has rejected an event with error 0x800423f4, The writer experienced a non-transient error.  If the backup process is retried,
the error is likely to reoccur.
. Changes that the writer made to the writer components while handling the event will not be available to the requester. Check the event log for related events from the application hosting the VSS writer.

Operation:
   PostSnapshot Event

Context:
   Execution Context: Writer
   Writer Class Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
   Writer Name: SqlServerWriter
   Writer Instance Name: SQL Server 2008:SQLWriter
   Writer Instance ID: {f2902d58-a841-4700-8cc2-03c61207060a}
   Command Line: "C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe"
   Process ID: 3380

We have also the following entry in Windows "Application" EventLog (3 times before EventID 24583):

Starting up database 'RollbackSnapshotTempDB{A80DC5F4-8004-4718-BCBC-AB66A9C66F08}'.

CHECKDB for database 'RollbackSnapshotTempDB{A80DC5F4-8004-4718-BCBC-AB66A9C66F08}' finished without errors on 2013-07-17 09:10:29.477 (local time). This is an informational message only; no user action is required.

Setting database option RECOVERY to SIMPLE for database RollbackSnapshotTempDB{A80DC5F4-8004-4718-BCBC-AB66A9C66F08}.

Setting database option SINGLE_USER to ON for database RollbackSnapshotTempDB{A80DC5F4-8004-4718-BCBC-AB66A9C66F08}.

[SER-VIS_s_r_l_]

I think this is not exact... With SSR 2010 we uncheck this option due to the issue I've reported, as suggested from Symantec Support. Anyway, the problem persists.

[criley]

So there looks to be some problem with the SQL VSS writer.

According to this (http://www.sqlcoffee.com/Troubleshooting116.htm), these temp DBs should be removed at the end of the backup process.

The fact that the SQL writer is failing makes me think that you need to contact Microsoft for help.

We (Symantec) can work with Microsoft if required but I think first port of call is MS support.

Let us know what MS have to say about this issue.

[ammacdo]

The problem on my server seems to be identical to SER-VIS. I have tried the SSR 2013 backup with and without the "Perform full VSS backup" box checked and it doesn't make a difference. We are also on SSR 2013 SP1, and after an SSR 2013 backup job completes the VSS writer "SqlServerWriter" shows a failed status with a non-retryable error. Restaring the "SQL Server VSS Writer" service clears the failed status. We do not have shadow copies enabled on any volumes.

I would like to work with Microsoft to get to the bottom of this but I have a lot of other projects that need my attention so my work around for now will be to disable SSR 2013 on this particular server and rely on Backup Exec 2010 to backup the entire server.

It seems to me that even if this does come down to being a problem with Microsofts VSS Writer, it's still to Syamtecs benefit to work directly with Microsoft to resolve this issue as it will make your customers happier and more likely to reccomend the product to others.

[SER-VIS_s_r_l_]

 I agree with ammacdo... Why I should pay for support from Symantec and when there is a problem with your software (yes... I think that the problem is in the implementation of VSS service on SSR2013, not in MS VSS) I should pay again for an case with MS?
 Anyway I've asked with a friend, that works as MS Support Technician, and I'm waiting for his reply about what MS thinks about his role in this enquiry. If they confirm that is a their problem, I'll decide how to proceed.

 Shadows copies are disabled on all drives of the server.

 Run a SSR 2013 backup job enabling the checkbox "Perform full VSS backup" gives me this result when BackupExec 2010 subsequently runs:

V-79-57344-867 - The Backup Exec SQL Agent was not used to create the last full, differential, or log backup of this database. You must use the SQL Agent to run a full backup before you can run a differential backup or transaction log backup

This is due to how the VSS works, is the result I expect. The same issue appear when using SQL Backup and any other backup software that uses VSS.

[criley]

Perhaps you misunderstood my earlier comments or maybe I did not word it well.

I'm not 'pointing the finger' at Microsoft here, I'm just saying it makes sense to get some input from their side. If you don't have an active support contract with MS, I take your point.

The bottom line is that the SQL writer looks like it has a problem. Of course, it could be something within SSR that is causing this - too early to say for sure at this point.

Do either of you have a support case open with us (Symantec) for this issue?

[SER-VIS_s_r_l_]

I haven't opened a case yet even if I have a support contract: I wanted to first see if I could find a workaround without having to open a ticket. Given that the thing is not so simple, I'll probably have to proceed in this way.

If it can help, the problem only occurs on one of the databases while for all other databases on the same instance, or even for the databases in the other instance running on the same server, the problem does not occur.
 

[criley]

Yes, I think a case is probably the way forward..

So what is different about the affected database when compared with the others? Understanding this may help to find a solution.

[SER-VIS_s_r_l_]

 Yes... That db is under heavy load due to presence of a software named "dbmoto" that synchronize and mirror all tables from and to other databases.
 Is not a real "sql replication" and the problem appear only with SSR2013 SP1, with SSR2010, BackupExec and/or SQL Backup there weren't any problems.
 

[ammacdo]

Well I'm sorry to say that my work around of using Backup Exec instead of SSR didn't pan out. We are now receiving a slightly different VSS error in the Backup Exec job log, but still pointing to some type of problem with the VSS writer. Also after the Backup Exec job ran the SQL VSS Writer was showing the failed status again.

I discovered that we are still on SQL Server 2008 R2 SP1 but SP2 has been out since last year (I just assumed Windows Update would show me when a new Service Pack was out), and they have 7 Cumulative Update Packages for SQL as well. I am working on duplicating the problem in our test environment and then will start installing SQL updates to see if it makes a difference.

Here is the link to the cumulative updates for SQL Server 2008 R2 SP2:

http://support.microsoft.com/kb/2730301

[Andreas_Horlach]

Thanks. I am also working on duplicating this on my end in a VM. 

[ammacdo]

Installing the updates didn't fix the issue, and our test environment which is virtually identical to the production environment does not have the same issue.

One difference between production and test is that production environment has two databases configured for replication with a SQL server being hosted by a vendor. The vendor set up the replication and I honestly don't know much about how it works but I'm starting to wonder if that is related.

[ammacdo]

Here are the error messages that show up in the Windows Application log during the SSR 2013 backup:

Log Name:      Application
Source:        SQLWRITER
Date:          7/19/2013 9:43:52 AM
Event ID:      24583
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      servername.domain
Description:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3724
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 10.0
Error message: Cannot drop the database 'RollbackSnapshotTempDB{29D77680-FE66-4B41-A51D-65C1223419E4}' because it is being used for replication.

Log Name:      Application
Source:        VSS
Date:          7/19/2013 9:43:52 AM
Event ID:      8229
Task Category: None
Level:         Warning
Keywords:      Classic
User:          N/A
Computer:      servername.domain
Description:
A VSS writer has rejected an event with error 0x800423f4, The writer experienced a non-transient error.  If the backup process is retried,
the error is likely to reoccur.
. Changes that the writer made to the writer components while handling the event will not be available to the requester. Check the event log for related events from the application hosting the VSS writer.

Operation:
   PostSnapshot Event

Context:
   Execution Context: Writer
   Writer Class Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
   Writer Name: SqlServerWriter
   Writer Instance Name: SQL Server 2008 R2:SQLWriter
   Writer Instance ID: {fdcb66ef-2402-4927-be63-6f61dca55565}
   Command Line: "C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe"
   Process ID: 2220