June 3, 2015 at 4:42 pm
I have started receiving seemingly random 2801 errors on SQL 2008 R2 SP3. We have a SQL function (fn_OrgFilterToLevel) that is called by a wrapper function (fn_OrgFilter). Periodically, on a few databases, we'll get 2801 errors stating that the nested function (fn_OrgFilterToLevel) has changed. Recompiling the function obviously fixes the error, but I haven't been able to determine the root cause. It seems to happen following weekends after a database upgrade has occurred. Part of our upgrade process involves tearing down then rebuilding transaction replication. Normal index and statistics maintenance also occurs after the upgrade. I verified that the create and modify dates in sys.objects for the function haven't changed for over 2 years, however the modify date for a dependent table was set to a date/time a few hours after the upgrade activity. I don't perform the replication activities personally, so I don't have an exact date/time when it was completed. Any ideas on what the root cause of the error might be? Please let me know if I can provide any other useful information.
June 4, 2015 at 7:16 am
Is it possible that the function gets a dependency "taken out from under it", so to speak, and then when accessed, there has somehow been no opportunity to re-establish that it has what it needs available? While I haven't seen this scenario before, I'm just wondering if checking into the details of the tear-down might reveal that the table it depends on gets dropped and re-created, and perhaps the solution is to simply add that function to the tear-down and re-creation processes ? I don't know if that's the way things are supposed to work, but it wouldn't surprise me... Anyone with better info, please feel free to provide...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 8, 2015 at 3:31 pm
It looks like adding an object to a transaction replication publication updates the modify_date in sys.objects. I believe that is what is pulling the dependency out from under the function. We're going to try adding the function to the publication to see if that resolves the issue. The error was only happening with one or two stored procedures out of many that call the function, so I'm thinking that those procedures were either compiling or executing when the publication was created and that's why SQL Server didn't just recompile the function when the dependent object was modified. Thanks for you input on this!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply