Edit

Share via

Delete a workload group

  • Article

Applies to: SQL Server Azure SQL Managed Instance

You can delete a workload group or resource pool using either SQL Server Management Studio or Transact-SQL.

Limitations

You can't delete a workload group if it contains active sessions.

If a workload group contains active sessions, deleting the workload group fails when the ALTER RESOURCE GOVERNOR RECONFIGURE statement is executed to apply the change. To avoid this problem, you can take one of the following actions:

  • Wait until all sessions in the affected group disconnect, and then execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
  • Explicitly stop sessions in the affected group by using the KILL command, and then execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement. If you decide that you don't want to explicitly stop sessions, re-create the group by using the original name and settings.
  • Restart the server. When the server restarts, the deleted group is deleted permanently.

Permissions

Deleting a workload group requires the CONTROL SERVER permission.

Delete a workload group using Object Explorer in SQL Server Management Studio

To delete a workload group using SQL Server Management Studio:

  1. In SQL Server Management Studio, open Object Explorer and expand the Management node down to and including Resource Pools.
  2. Expand Resource Pools down to and including the Workload Groups node in the resource pool that contains the workload group to be deleted.
  3. Open the context menu for the workload group and select Delete.
  4. In the Delete Object window, the workload group is listed in the Object to be deleted list. To delete the workload group, select OK.

Delete a workload group using resource governor properties

To delete a workload group by using the resource governor properties page:

  1. In Object Explorer, expand the Management node down to and including Resource Pools.
  2. Open the context menu for the resource pool that contains the workload group to be deleted and select Properties. This opens the Resource Governor Properties page.
  3. In the Workload groups for resource pool window, select the row for the workload group to be deleted. Open the context menu and select Delete.
  4. To delete the workload group, select OK.

Delete a workload group using Transact-SQL

To delete a workload group using Transact-SQL:

  1. Execute the DROP WORKLOAD GROUP statement specifying the name of the workload group to delete.
  2. Before you execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement, verify that there are no active requests in the workload group being deleted. If there are active requests, ALTER RESOURCE GOVERNOR fails. For more information and for solutions, see Limitations and restrictions.
  3. Execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example

The following example deletes a workload group named groupAdhoc and makes the new configuration effective.

DROP WORKLOAD GROUP groupAdhoc;

ALTER RESOURCE GOVERNOR RECONFIGURE;

Related content