Drop failed for ResourcePool: Remove all bindings

SQL Server 2014 "Drop failed for ResourcePool" error with description "Cannot drop resource pool 'Pool_DatabaseName' because it is bound to a database. Remove all bindings to this resource pool before dropping it." is one of the problems I experienced while setting up SQL Server in-Memory database on SQL Server 2014 using the sample AdventureWorks2014 database. The resource pool is created on target SQL Server instance Resource Governor within the SQL batch script to convert AdventureWorks disk-based database to memory optimized SQL Server database.

This SQL Server tutorial is demonstating at the same time "How to drop resource pool bound to a in-memory database on SQL Server 2014".

After I download SQL Server 2014, I restored sample database AdventureWorks2014.
While creating sample in-Memory database for SQL Server 2014 on my Developer Edition test machine, I experienced a few errors and tried to delete resource pools that were created during the execution of the database creation script.

Delete Resource Pool using SQL Server 2014 Management Studio

I drilled down on the Object Explorer window on SQL Server Management Studio (SQL2014).
Under Management node, administrators can see the Resource Governor node.
Under Resource Governor, the Resource Pools are listed.

As seen in below screenshot, I tried to Delete resource pool created and bound to AdventureWorks2014 database.

delete resource pool using SQL Server Management Studio

Delete resource pool dialog screen is as follows. Press on OK button to continue with drop resource pool task.

drop resource pool under SQL Server 2014 Resource Governor

Unfortunately I experienced the following error message.

drop failed for ResourcePool

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for ResourcePool 'Pool_AdventureWorks2014'. (Microsoft.SqlServer.Smo)
For help, click: Drop ResourcePool link
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot drop resource pool 'Pool_AdventureWorks2014' because it is bound to a database. Remove all bindings to this resource pool before dropping it. (Microsoft SQL Server, Error: 10931)
For help, click: EventId 10931

Remove all bindings to resource pool using sys.sp_xtp_unbind_db_resource_pool

If you plan to drop the database that the Reporce Pool is bound to, then you can first drop the related database. After database drop, resource pool can be deleted without any problem.

But if you plan to keep the database, first of all the bind between database and the resource pool should be deleted by executing the following SQL Server command:


sys.sp_xtp_unbind_db_resource_pool '[database_name]'

Code

Note that SQL database administrators should replace the "[database_name]" with the name of the database, not the name of the resource pool

After the binding has been broken with the sp_xtp_unbind_db_resource_pool stored procedure, to take the changes on effect you should take the database offline and then bring back to online. Otherwise, SQL Server will prevent dropping of the resource pool untill the target database is restarted.

It is easier to execute below SQL script to take the database to offline and back to online to enable the changes related with resource pool binding take affect.


ALTER DATABASE InMemory SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE InMemory SET ONLINE

GO

Code

Drop Resource Pool and Reconfigure Resource Governor

After all above steps are completed, an SQL Server database administrator can drop the resource pool as follows:


DROP RESOURCE POOL [resource_pool_name]

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

Code

Also note that after the resource pool is dropped from the Resource Governor, the Resource Governor is set to state "pending reconfigure".
If the Resource Governor state is in pending reconfigure state, the SQL command "ALTER RESOURCE GOVERNOR RECONFIGURE;" can be executed on the Query Editor screen.
Another option is using the SSMS (SQL Server Management Studio) by highlighting the Resource Governor node, then by right-click to display the context node and choose the Reconfigure menu option.

For more information on sys.sp_xtp_unbind_db_resource_pool T-SQL command, please refer to SQL Server Books Online

SQL Server programmers can find a similar info on sys.sp_xtp_bind_db_resource_pool again at BOL