Troubleshooting upgrade from sql 2005 to sql 2008R2
-
I've inherited a legacy IIS 7.0 website (vb.net website, not an application) that was running against an MSSQL 2005 Database. Recently we upgraded to MSSQL 2008 R2. Now, when users go to certain pages, we get a SQL Connection timeout error. Investigating, it says this can happen when datareaders are used and connections aren't properly closed. I tried adding try/catch/finally statements, but I can still get the error. I've also updated the SQL Client to use the same version on both the SQL server and the Web Server, to no avail. I pull a netstat when the issue happens, and there's literally thousands of connections to the SQL server (from the Web Server).. based on the one page (trying) to load. This particular page makes a simple call to populate an Org diagram. Fairly straight forward. But for some reason, the page has almost taken off without control and uses up all the available connections to the SQL server. Any thoughts on how I might troubleshoot/resolve this issue would be greatly appreciated! Update: I've found that when the database was upgraded from 2005 to 2008R2, some NULL columns were converted to columns with a 0 value..which then caused the stored procedure for the page to infinite loop and lock the connections (again.. not my code!). Any idea why that might have happened? Was the database not restored/converted properly?