How to: Delete a Workload Group or Resource Pool (SQL Server Management Studio)

You can delete a workload group or resource pool by using SQL Server Management Studio. Use the following steps to delete a workload group named groupAdmin, which is in the resource pool named poolAdmin. After you delete groupAdmin, delete the poolAdmin resource pool.

Deleting or Moving a Workload Group

If a workload group contains active sessions, deleting or moving the workload group to a different resource pool will fail when the ALTER RESOURCE GOVERNOR RECONFIGURE statement is called to apply the change. To avoid this problem, you can take one of the following actions:

  • Wait until all the sessions from the affected group have disconnected, and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

  • Explicitly stop sessions in the affected group by using the KILL command, and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement. If you decide that you do not want to explicitly stop sessions after you use Delete but before you stop active sessions, re-create the group by using the original name and move the group to the original resource pool.

  • Restart the server. After the restart process is completed, the deleted group will not be created, and a moved group will use the new resource pool assignment.

Deleting a Resource Pool

You cannot delete a resource pool if it contains workload groups.

To delete a workload group

  1. In Object Explorer, recursively expand the Management node down to and including Resource Pools.

  2. Recursively expand Resource Pools down to and including poolAdmin/Workload Groups.

  3. Right-click groupAdmin, and then click Delete.

  4. In the Delete Object window, groupAdmin is listed in the Object to be deleted list. To delete groupAdmin, click OK.

To delete a resource pool

  1. In Object Explorer, recursively expand the Management node down to and including Resource Governor.

  2. Right-click poolAdmin, and then click Delete.

  3. In the Delete Object window, poolAdmin is listed in the Object to be deleted list. To delete poolAdmin, click OK.

    Note

    If the resource pool that you are trying to delete contains a workload group, this action will fail.