Applies To: SQL Server 2014, SQL Server 2016 Preview
Binds the specified In-Memory OLTP database to the specified resource pool. Both the database and the resource pool must exist prior to executing sys.sp_xtp_bind_db_resource_pool.
This system procedure creates a binding between the Resource Governor pool identified by resource_pool_name, and the database identified by database_name. It is not required that the database has any memory-optimized objects at the time of binding. In the absence of memory-optimized objects, there is no memory taken from the resource pool. This binding will be used by Resource Governor to manage memory allocated by In-Memory OLTP allocators as described below.
If there is already a binding in place for a given database, the procedure returns an error. In no event may a database have more than one active binding.
Applies to: SQL Server (SQL Server 2014 through current version).
The name of an existing In-Memory OLTP enabled database.
The name of an existing resource pool.
When an error occurs sp_xtp_bind_db_resource_pool returns one of these messages.
- Database does not exist
Database_name must refer to an existing database. If there is no database with the specified ID, the following message is returned:
Database ID %d does not exist. Please use a valid database ID for this binding.
Msg 911, Level 16, State 18, Procedure sp_xtp_bind_db_resource_pool_internal, Line 51 Database 'Hekaton_DB213' does not exist. Make sure that the name is entered correctly.
- Database is a system database
In-Memory OLTP tables cannot be created in system databases. Thus it is invalid to create a binding of In-Memory OLTP memory for such a database. The following error is returned:
Database_name %s refers to a system database. Resource pools may only be bound to a user database.
Msg 41371, Level 16, State 1, Procedure sp_xtp_bind_db_resource_pool_internal, Line 51 Binding to a resource pool is not supported for system database 'master'. This operation can only be performed on a user database.
- Resource Pool does not exist
The resource pool identified by resource_pool_name must exist prior to executing sp_xtp_bind_db_resource_pool. If there is no pool with the specified ID, the following error is returned:
Resource Pool %s does not exist. Please enter a valid resource pool name.
Msg 41370, Level 16, State 1, Procedure sp_xtp_bind_db_resource_pool_internal, Line 51 Resource pool 'Pool_Hekaton' does not exist or resource governor has not been reconfigured.
- Pool_name refers to a reserved system pool
The pool names “INTERNAL” and “DEFAULT” are reserved for system pools. It is not valid to explicitly bind a database to either of these. If a system pool name is entered, the following error is returned:
Resource Pool %s is a system resource pool. System resource pools may not be explicitly bound to a database using this procedure.
Msg 41373, Level 16, State 1, Procedure sp_xtp_bind_db_resource_pool_internal, Line 51 Database 'Hekaton_DB' cannot be explicitly bound to the resource pool 'internal'. A database can only be bound only to a user resource pool.
- Database is already bound to another Resource Pool
A database can be bound to only one resource pool at any time. Database bindings to resource pools must be explicitly removed before they can be bound to another pool. See sys.sp_xtp_unbind_db_resource_pool (Transact-SQL).
Database %s is already bound to resource pool %s. You must unbind before you can create a new binding.
Msg 41372, Level 16, State 1, Procedure sp_xtp_bind_db_resource_pool_internal, Line 54 Database 'Hekaton_DB' is currently bound to a resource pool. A database must be unbound before creating a new binding.
When successful, sp_xtp_bind_db_resource_pool returns the following message.
- Successful Binding
When successful, the function returns the following success message, which is logged in the SQL ERRORLOG
A resource binding has been successfully created between the database with ID %d and the resource pool with ID %d.
The following code example binds the database Hekaton_DB to the resource pool Pool_Hekaton.
sys.sp_xtp_bind_db_resource_pool 'Hekaton_DB', 'Pool_Hekaton'
Both the database specified by database_name and the resource pool specified by resource_pool_name must exist prior to binding them.
Requires CONTROL SERVER permission.