Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 22 '12 at 15:52

This question came from our site for professional and enthusiast programmers.

1  
Have you reviewed the KB article on this error? In summary, if DBCC doesn't find any problems and if the problem is reproducible then it may be a bug in SQL Server and you should contact Microsoft. –  Pondlife Dec 20 '11 at 10:33
    
@Pondlife The problem for us is that under specific circumstances this happens we can't run DBCC while the snapshot still exists without more effort than I'd like. I was hoping that someone had run into something similar or had a gut feeling what was causing this. With that said we'll go ahead and modify the test sequence so that we can use DBCC :) –  Joel Abrahamsson Dec 20 '11 at 11:39
1  
We ran into a few similar issues in the past, ended up using only scripts and bulk loads off csv to set up the sandbox for our integration tests. That does work for us. –  AlexKuznetsov Dec 20 '11 at 15:58
    
@AlexKuznetsov Thanks. Although that doesn't solve our issues hearing that others had the same problem pushed us to trying to find an alternative solution. We're now running the integration tests using a local installation of SQL Server on the build server which seems to work just fine. –  Joel Abrahamsson Dec 21 '11 at 9:23
1  
@JoelAbrahamsson From BOL -Database snapshots are dependent on the source database. Therefore, **using database snapshots for reverting a database is not a substitute for your backup and restore strategy**. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that. It could be a transient IO subsystem problem or (more likely) bad memory. So suggest you to run memory diagnostics and then IO subsystem diagnostics. –  Kin Jul 1 '13 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.