Database.Register-method resulting in deadlock

syspgesyspge Posts: 2
edited June 30, 2006 12:41PM in SQL Toolkit Previous Versions
Hi.

I'm working with two databases with the SQL Toolkit copying data from a source to a destination database.
Before the copying itself of data, I perform operations on the destination database and I start a transaction as the first thing I do. I do the following:

1) Start a transaction on the destination db.
2) Perform changes on destination db.
3) Invoke RedGate toolkit to copy data from souce to destination:
- Instantiate a RedGate.SQLCompare.Engine.Database on the source database. Runs the Register() method on it with db-connection informations.
- Instantiate a RedGate.SQLCompare.Engine.Database on the destination database. Runs the Register() method on it with db-connection informations * DEADLOCK HAPPENS HERE *
4) If RedGate-copy went well I commit the destination transaction. Otherwise it is rolled back.

When I inspect what happens with SQL Profiler. The Register method fires off a number of sql statements, and the crutial part starts when reading from the sysobjects table which is apparently locked:

---
SELECT sysusers.name as ownername, o.name, o.id FROM sysobjects o
LEFT JOIN sysusers WITH (NOLOCK) ON sysusers.uid=o.uid
WHERE o.xtype IN ('FN', 'IF', 'P', 'S', 'TR', 'TF', 'U', 'V', 'D', 'SN')
---

How should I perform this operation? I hope you can help me out on this.


Best regards,
Per

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Per,

    I'm not sure what's going on there... when you say deadlock do you mean you get an error back from SQL Server saying there is a deadlock? Or is the application just not responding?

    Why are you migrating data and then instantiating RedGate.SQLCompare.Database? This would be used for schema operations rather than data operations.

    Hopefully we can get this sorted out for you.
Sign In or Register to comment.