A Few days ago, I was working on resource governor implementation on my test server. Once I implemented & tested, I had to delete the resource governor settings from the test server. But Unfortunately, I got stuck because of the given below error. It did not allow me to delete the resource governor properly. Let me explain the error and its solution in details.
Message Number: 10920
Severity : 16
Error Message: Cannot %S_MSG user-defined function ‘%.*ls’. It is being used as a resource governor classifier.
Error Generation:
Given below is the script that I tried to execute but it resulted with the following error.
DROP FUNCTION dbo.rgclassifier_MAX_CPU
Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function ‘rgclassifier_MAX_CPU’. It is being used as a resource governor classifier.
Ooopps…… I am unable to drop the above classifier function.
Resolution:
If you look at the error description, it seems that classifier function is in use of resource governor, so you cannot drop this function. Then I thought of disabling it and tried to drop after that but I could not :(.
So what is the solution? Basically, you need to assign all new sessions to the default workload by eliminating the classifier function from the resource governor configuration of your server. When there is no user defined classifier function assigned as a classifier function, you can easily drop the classifier function. Given below is the script.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO
Now, you can easily drop the classifier function.
DROP FUNCTION dbo.rgclassifier_MAX_CPU
SQL Server Portal 