February 22, 2007 at 1:38 pm
Using SQL 2000 Server Enterprise Manager to modify user defined functions was repeatedly producing a stalled/hung application. Each time I tried to change and save the function the application hangs and I had to kill it with the task manager.
I decided to DROP FUNCTION and CREATE FUNCTION in the Query Analyzer. Here the same result: the DROP FUNCTION produces a long running query which never seems to end.
What can be the reason that DROP FUNCTION takes so long? It's a development system and I'm the only user.
February 22, 2007 at 4:24 pm
While the DROP is running, look in SP_WHO for a blocking process.
February 23, 2007 at 1:04 am
you'd just do so much better to not use EM for this and use QA or similar tool and code using a drop and create. Depending upon the dependencies an alter may go slow. Experience has shown drop and create is better than alter.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 23, 2007 at 7:09 am
Thanks, I'll try that next time it happens. Can't seem to reproduce the problem today. I did have views open which used the function yesterday but today I did the same and it did not block.
February 25, 2007 at 12:50 pm
What about replication? If your function is being used by articles in one or more publications, then any drop or alter statements could be blocked until replication stops using the object.
Rick
townsends.ca
February 26, 2007 at 1:48 pm
Nope, never ventured into the realm of replication with MS SQL.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply