We have a scenario in which we, during integration tests, make a backup of an existing database and restore it under a new name. We then create a snapshot of our "new" database. After each test we restore the database from the snapshot so that different tests don't affect each other. This works well except for in a specific scenario where the restore fails with the following error message:
System.Data.SqlClient.SqlException : Unable to open the physical file "\server\path\MyDb_IntegrationTestsSnapshot.ss". Operating system error 58: "58(failed to retrieve text for this error. Reason: 15105)". Could not open File Control Bank (FCB) for invalid file ID 2 in database 'MyDb_IntegrationTestsSnapshot'. Verify the file location. Execute DBCC CHECKDB. RESTORE DATABASE is terminating abnormally.
The code that produces that looks like this:
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDb"].ConnectionString))
{
con.Open();
var databaseName = GetDatabaseName();
var sql =
string.Format(
@"use master
alter database {0} set single_user with rollback immediate;
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '" + GetSnapshotName() + @"';
ALTER DATABASE {0} SET MULTI_USER WITH NO_WAIT",
databaseName);
var command = new SqlCommand(sql, con);
command.ExecuteNonQuery();
con.Close();
SqlConnection.ClearAllPools();
}
In almost all cases the above code works just fine, however when the test executes a few specific SQL commands that uses temporary tables AND performs deletes the above error occurs. Note however that in other cases commands that uses temporary tables works just fine. Likewise, other deletes work well as well.
Furthermore, this ONLY happens when we run the tests on our build server against a separate database machine. Running the tests locally against a local SQL Server instance works fine. So does running the tests locally against the same database server that the build server uses.
Finally, note that other tests that uses this rollback setup works fine on the build server. Even the rollback after the now failing tests ran just fine at a previous point in time, but have now stopped working. The only real difference between now and then seems to be that some additional data has been added.
We're using SQL Server 2008 R2 and .NET 4.
UPDATE: Based on the answer by Pondlife we attempted to run DBCC but without success as it reported the snapshot marked as suspect. We then checked the logs and found "Error: 17053, Severity: 16, State: 1.". Trying to find information about that we found that one cause of this could be when using VMWare and having a PVSCSI adapter configured. The build server does run on VMWare (the db server is native) but it isn't configured with a PVSCSI adapter but a LSI Logic SAS adapter.
UPDATE 2: We've narrowed this down to tests which cause relatively many (330) deletes. If we change a test whose teardown usually causes this error so that the actual test code only triggers a single delete instead of hundreds it works fine.
