Red Gate forums :: View topic - File Lock Not Released
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

File Lock Not Released

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
nickyzzi



Joined: 12 May 2009
Posts: 7

PostPosted: Tue May 19, 2009 4:58 pm    Post subject: File Lock Not Released Reply with quote

I'm using the SDK to compare a live Database against a BackupDatabaseSource. I'm having trouble getting the file lock on the BackupSet released. What I'm doing is

1) Creating a backup on my live database (DB2) using SQLDMO
2) Synchronizing changes in DB1 to my live database (DB2)
3) Comparing my live database to the backupset I created in step one

The second time I try to run this sequence I get this error when I try to create the backupset:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device '\\nyzzi\dbbackups\MDIPracticeMaster_dev.bak'. Operating system error 32(The process cannot access the file because it is being used by another process.). [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

After running several tests, I determined that the lock is not being released by the SDK in step 3 above. Here is the code for step 3:

public void Compare(string conStr) {
try {
comparisons.Clear();
var fullBackupFilePath = string.Format(@"{0}\{1}.bak", BackupFilePath, TargetDatabaseName);
using (BackupSetDatabase backupDB = new BackupSetDatabase())
using (Database liveDB = new Database())
using (ComparisonSession session = new ComparisonSession()) {
IList<string> files = new string[] { fullBackupFilePath };
IList<string> passwords = new List<string>();

//BackupSet
IList<IBackupSet> backupSets = backupDB.GetBackupSets(files, passwords);
IBackupSet backupSetToUse = backupSets[backupSets.Count - 1];
BackupDatabaseSource backupDBSource = new BackupDatabaseSource(files, passwords, backupSetToUse);
backupDB.RegisterForDataCompare(backupDBSource.ToConnectionProperties(), Options.Default);

//Live DB
ConnectionProperties connectionProperties = new ConnectionProperties(ServerName, SourceDatabaseName, UserName, Password);
liveDB.RegisterForDataCompare(connectionProperties, Options.Default);

//Add Table Mappings
TableMappings mappings = new TableMappings();
var kbEntities = ResourceAccess.GetKBEntites(conStr);
kbEntities.ForEach(delegate(KBEntity kbEntity) {
mappings.Add((TableMapping)mappings.Join(backupDB.Tables[kbEntity.TableName], liveDB.Tables[kbEntity.TableName]));
});
session.CompareDatabases(backupDB, liveDB, mappings);

backupDB.Close();
backupDB.Dispose();
liveDB.Dispose();

//Compare tables
foreach (TableMapping mapping in mappings) {
var difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];

if (difference != null) {
foreach (Row row in difference.ResultsStore) {
var comparison = new KBComparison { EntityTableName = mapping.Obj1.FullyQualifiedName.Replace("[dbo].[", "").Replace("]", "") };
if (row.Type != Row.RowType.Same) {
int fieldIndex = 0;
foreach (FieldPair field in difference.ResultsStore.Fields) {
int field1 = field.OrdinalInResults1;
int field2 = field.OrdinalInResults2;

if (field1 != field2) {
object value1 = row.Values[field1];
object value2 = row.Values[field2];
if (value1 == null) value1 = "NULL";
if (value2 == null) value2 = "NULL";

if (row.FieldDifferent(fieldIndex)) {
comparison.FieldChanges += string.Format("{0} changed from '{1}' to '{2}', ", field.Field(false).Name, value1.ToString(), value2.ToString());
};
} else {
comparison.EntityIdFieldDescription = field.Field(false).Name;
comparison.EntityId = Convert.ToInt32(row.Values[field1]);
}
fieldIndex++;
}
comparisons.Add(comparison);
}
}
}
}
backupDB.Close();
backupDB.Dispose();
}
} catch (Exception ex) {
Console.WriteLine(ex.Message);
throw;
}
}
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Fri May 22, 2009 2:21 pm    Post subject: Reply with quote

Thanks for your post.

I'm not sure what could be locking the file. I was able to perform another backup before I had disposed of the database object.

Can you check what actually has a handle on the backup file using process monitor from sysinternals? (Find >> Find handle or dll)?
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
nickyzzi



Joined: 12 May 2009
Posts: 7

PostPosted: Fri May 22, 2009 2:43 pm    Post subject: Reply with quote

Thanks Chris,

I ended up changing how I'm doing this compare to avoid the problem. IIS is the process retaining the lock on the file (as the code was run from a web project).
Back to top
View user's profile Send private message
hemantgaur



Joined: 16 Dec 2009
Posts: 20

PostPosted: Thu Feb 18, 2010 4:25 pm    Post subject: Reply with quote

We might be seeing a similar issue where IIS is holding up a red-gate object which is connected to a database.

Request to nickyzzi OR Chris,
nickyzzi, can you please provide some more information on how you ended up avoiding this problem?

Chris,
If nickyzzi is no longer active in this forum, did he mention to you the solution offline? Or have you guys figured out a work around?

Would appreciate any help.
Thanks.
Back to top
View user's profile Send private message
nickyzzi



Joined: 12 May 2009
Posts: 7

PostPosted: Thu Feb 18, 2010 5:35 pm    Post subject: Reply with quote

I never found a solution to this problem. I just stopped doing the backups... sorry I can't be of more help.
Back to top
View user's profile Send private message
hemantgaur



Joined: 16 Dec 2009
Posts: 20

PostPosted: Mon Feb 22, 2010 6:52 pm    Post subject: Reply with quote

Thank you for replying.
Back to top
View user's profile Send private message
robtaylor82



Joined: 24 May 2010
Posts: 1

PostPosted: Mon May 24, 2010 11:04 am    Post subject: Reply with quote

Hi there,

We have recently started using the SDK component and have ran into exactly the same problem. Every time we load a backup to perform a comparison, the file lock remains indefinitely - even after closing / disposing all of the objects.

Is there a work around for this - we really want to use this functionality?

Here is the code we are using:

Code:

RedGate.SQLCompare.Engine.Database targetDatabase = new RedGate.SQLCompare.Engine.Database();
BackupSetDatabase backupSetDatabase = new BackupSetDatabase();
ComparisonSession session = new ComparisonSession();

StringCollection tablesContainingDifferences = new StringCollection();

try
{
    //load backup
    IList<string> backupFiles = new[] { backupPath };
    IList<IBackupSet> backupSets = backupSetDatabase.GetBackupSets(backupFiles);
    IBackupSet backupSetToUse = backupSets[backupSets.Count - 1];

    BackupDatabaseSource backupDatabaseSource = new BackupDatabaseSource(backupFiles, new List<string>(), backupSetToUse);
    backupSetDatabase.RegisterForDataCompare(backupDatabaseSource.ToConnectionProperties());

    //load target database
    targetDatabase.ConnectionProperties = new ConnectionProperties();
    targetDatabase.ConnectionProperties.ConnectionString = database.ConnectionString;
    targetDatabase.ConnectionProperties.DatabaseName = new SqlConnectionStringBuilder(database.ConnectionString).InitialCatalog;
    targetDatabase.RegisterForDataCompare(targetDatabase.ConnectionProperties);

    TableMappings mappings = new TableMappings();

    foreach (string table in targetTables)
    {
        TableMapping tableMapping = (TableMapping) mappings.Join(backupSetDatabase.Tables[table], targetDatabase.Tables[table]);
        mappings.Add(tableMapping);
    }

    //compare databases
    session.CompareDatabases(backupSetDatabase, targetDatabase, mappings);
    session.Options.ComparisonOptions = ComparisonOptions.UseChecksumComparison;

    foreach (TableDifference difference in session.TableDifferences)
    {
        if (difference.DifferencesSummary.DifferenceCount() > 0)
        {
            tablesContainingDifferences.Add(difference.Name);
        }
    }

}
finally
{
    targetDatabase.Dispose();
    backupSetDatabase.Dispose();
    session.Dispose();
}


Cheers,
Rob.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group