Error applying ddl from sqlcompare on a virtual database...

jschwarz

I have a procedure that refreshes a virtual database from the most recent backup file. After this finishes, I attempt to apply ddl changes from my SVN repository using SQL Compare, and I get the following error...

Synchronizing databases (from DB1 to DB2)
Error: Synchronization of 'Scripts.Hub' and 'tstsql.Hub' failed:
The operating system returned error 31(A device attached to the system is not functioning.) to SQL Server during a write at offset 0x000003ec860000 in file
'F:\VirtualDatabases\Hub_Virtual.vmdf'. 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. Could not create constraint. See previous errors.

Does anyone have an idea where to look to resolve this problem?

jschwarz

More information on my error...

This is a VMWare virtual server running Windows 2003 x64 SP2.

In the SQL Server log there is an additional error message that precedes the "Error 31" error...

Error: 823, Severity: 24, State: 17

Also, the virtual database that is giving this error is open and available, and I am able to insert data into tables with a SQL session and standard insert commands.

jschwarz

Running a DBCC CHECKDB on the virtual database in question produces this group of errors...

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 823, Level 24, State 3, Line 1
The operating system returned error 31(A device attached to the system is not functioning.) to SQL Server during a write at offset 0x000000001a4000 in file 'F:\VirtualDatabases\ArtisanHub_ArtisanHub1_Virtual.vmdf'. 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.

javen

Hi,

We will need to get your logs from you, I will make contact

jschwarz

While a version upgrade may have helped, I did receive the error again after the upgrade, although much less frequently.

The important thing that I found was that if I made sure to delete all virtual data files and virtual log files before refreshing the database again I would not get an error.

Since I added a function to remove all previous files, I have not received the error again.

One Observation - When dropping a database via SSMS (right click, delete) the virtual data and log files get removed from the file system, however, when dropping the database via a job that executes a stored proc, the virtual data files do not get deleted from the file system.