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.