SQL SERVER – Fix: Error: 10920 Cannot drop user-defined function. It is being used as a resource governor classifier

If you have not read my SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor yesterday’s detailed primer on Resource Governor, I suggest you go ahead and read it before continuing this article. After reading the article the very first email I received was as follows:

“Pinal,

I configured resource governor on my development server and it worked fine with tests I ran. After doing some tests, I decided to remove the resource governor and as a first step I disabled it however, I was not able to drop the classification function during the process of the clean up. It was continuously giving me following error.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function myudfname. It is being used as a resource governor classifier.

Would you please give me solution?”

The original email was really this short and there is no other information. I am glad he has done experiments on development server and not on the production server. Production server must not be the playground of the experiments. I think I have covered the answer of this error in an earlier blog post.

If the user disables the Resource Governor it is still not possible to drop the function because it can be enabled again and when enabled it can still use the same function. Here is the simple resolution of the how one can drop the classifier function (do this only if you are not going to use the function).

The reason the classifier function can’t be dropped because it is associated with resource governor. Create a new classified function for your resource governor or just assign NULL as described in the following T-SQL Script and you will be able to drop the function without error.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.UDFClassifier
GO

I am glad that user asked me question instead of doing something radically different, which can leave the server in the unusable state. I am aware of this only method to avoid this error. Is there any better way to achieve the same?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor
Next Post
SQL SERVER – Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video

Related Posts

2 Comments. Leave new

  • Good tip, thanks Pinal.

    It is funny how you cannot alter the function even when the Governor is disabled.

    Reply
  • This issue is also applicable when you want to alter a user-defined Scalar function. you can replace the drop statement with your alter statement like this:

    —Step1

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
    GO
    ALTER RESOURCE GOVERNOR DISABLE
    GO
    ———————————————–
    — Step 2: Alter function to Route Workload Group
    ———————————————–
    Alter FUNCTION [dbo].[f_ResourcePoolClassifier]()
    RETURNS SYSNAME
    WITH SCHEMABINDING
    AS
    BEGIN
    DECLARE @WorkloadGroup AS SYSNAME
    IF SUSER_NAME() IN (
    ‘domainName\username’
    )
    SET @WorkloadGroup = ‘Scalar-valued_FunctionsName’
    ELSE
    SET @WorkloadGroup = ‘default’
    RETURN @WorkloadGroup
    END

    GO

    — Step 3: Enable RESOURCE GOVERNOR

    ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION=Scalar-valued_FunctionsName);
    GO
    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO

    Reply

Leave a Reply

Menu