January 24, 2013 at 11:23 am
This is our first go around with resource governor so we referred to the following msdn article to set it up initially. We set up 2 workload groups & resource pools: GroupAdhoc & GroupAdmin. In the classification function, we assigned an AD group to the GroupAdhoc and used is_MEMBER(SYSADMIN) to assign to the Group Admin. After running this I started looking to see if it was all setup correctly. I noticed teh two workload groups that I created were assigned to the default resource group so I proceeded to move the GroupAdhoc workload group to the GroupAdhoc resource pool, no big deal. I then moved the GroupAdmin workload group to GroupAdmin resource pool. I then ran the statement to reconfigure the resource governor and I get teh following message:
Msg 10904, Level 16, State 2, Line 1
Resource governor configuration failed. There are active sessions in workload groups being dropped or moved to different resource pools. Disconnect all active sessions in the affected workload groups and try again.
Obviously I am connected via a sysadmin login so the GroupAdmin workload group still has an active login so now I am stuck with not being able to reconfigure the resource governor. Is there a way to force this change even with an active login? Or what happens if I bounce the server, will the new changes take affect?
Looking back at it now, I probably didn't need the GroupAdmin workload group or resource pool. I could have left it to the defaults.
Any thoughts on what my next step should be?
Thanks!
January 24, 2013 at 12:27 pm
Nevermind...I restarted SQL Server and the new changes went into affect. I thought maybe I was stuck in a loop but thankfully not..
August 16, 2023 at 7:15 pm
I know it's an old post, but I thought I'd mention what helped me.
The message indicates that sessions are active inside workgroups that are changing.
To get past the error, you have to wait until those sessions close or you can kill them.
Restarting the service certainly has the same effect.
Which sessions need to disconnect? These ones:
SELECT
dwg.name [current work group],
dwg.pool_id [current resource pool],
wg.name [configured work group],
wg.pool_id [configured resource pool],
s.*
FROM
sys.dm_exec_sessions s
INNER JOIN
sys.dm_resource_governor_workload_groups dwg /* existing groups */
ON dwg.group_id = s.group_id
LEFT JOIN
sys.resource_governor_workload_groups wg /* configured groups */
ON wg.group_id = s.group_id
WHERE
isnull(wg.group_id, -1) <> dwg.pool_id
ORDER BY
s.session_id;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply