I have inherited a very old and poorly written stored procedure with nested fetch statements. This code processes a large amount of data by copying from one database into another. This process was working fine until updates were made to indexes. This past weekend the process was deadlocks on tables and failed to insert data. Added to this bad stored procedure the database we are inserting into is loaded with multiple triggers on just about every table.
I have reverted the indexes back to where they were but the inner fetch stays in an infinite loop. We have also tried rebooting the server to clear out any data stuck in memory. We are running SQL Server 2008R2
I can provide code if needed. Any help is greatly appreciated. We are impacting production at this point.