I am supporting an application whose code my company did not write, and was originally developed back in 2000-2002, by a company that no longer exists. The code, while not great to maintain, thankfully has been working with only minor issues every few months, and almost never requires a code change (over the past several years).

The company that the application was written for just migrated their SQL Server 2000 database to SQL Server 2005, leaving the database in SQL Server 2000 (80 I believe) compatibility mode. Since this update several of the places in the code in multiple applications are now throwing SQL timeout errors (error code -2).

We can point the applications to the old database however with the same queries/same stored procedures, and the queries run with no problem. However when we point them to the new SQL Server 2005 instance, they fail.

Checking the database to see the status of the users (sp_who2), we see that there are locks happening.

I am reluctant to make any changes to the code, and am wondering if there should even be changes made to the DB, since this code has been in production for over a decade. I am not a SQL guru, and don't even quite know what to look for. I am wondering if there isn't some property that governs how the queries run that had been changed by the original company, that wasn't documented and changed when the migration happened. Anyone have any ideas of what could cause multiple applications pointed to the same DB to begin having timeout issues after migration? This is happening not just with stored procedures, but even on a .NET DataSet Fill operation.

migrated from stackoverflow.com Mar 26 '14 at 5:03

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

  • 1
    You are best updating stats and rebuilding indexes as these should be a post migration task when moving from 2000 to 2005. Also, just to make sure data is consistent, perform a checkdb with datapurity option. – Kin Shah Mar 26 '14 at 12:08
  • This had been done when the migration completed. One of the fixes just implemented that at least fixed a SPROC call was an inner join in a subquery that apparently caused an infinite loop. For some reason, the execution/handling of this query is different in SQL2005 than it was in 2000. It runs fine in 2000 on the same data, with the same query. – tostringtheory Mar 26 '14 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.