SQL Server Performance

Error in Linked Server After migration

Discussion in 'SQL Server 2005 General Developer Questions' started by dineshasanka, Aug 17, 2006.

  1. dineshasanka Moderator


    Hi All,
    We have SQL Server which has a link to mysql database. end of the day, we are trasnfering data from mySQL to MSSQLServer. This was workign fine with SQL Server 2000 but after migrating to SQL Server 2005 we are getting following error.


    [Microsoft SQL-DMO (ODBC SQLState: 40001)] Error 1205: [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #13) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.



    ----------------------------------------

  2. satya Moderator

    Are you getting this everytime the query runs?
    Have you performed the relevant index optimization on those tables

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. dineshasanka Moderator

    yes I am getting this every time. index performance is already done on sql server 2000 so we just did a re-indexing

    ----------------------------------------

  4. satya Moderator

    Not sure whether the KBA:http://support.microsoft.com/kb/821537/ is any help, check it out.
    Its not ideal but have you tried using transaction isolation level on SQL from serializable to read committed.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. dineshasanka Moderator

    Thanks again for the advise.
    That link asks to update SP3 for SQL Server 2000, but I have SQL Server 2005 with latest SP1.

    I have set Isolationlevel to REPEATABLE READ to ignore the dirty reads. Even I have doubt whether this is the cause

    ----------------------------------------

  6. dineshasanka Moderator

    Well, Identified the issue. When I changed the isolationlevel it works.


    ----------------------------------------

Share This Page