Edit

sys.sp_xtp_bind_db_resource_pool (Transact-SQL)

  • Article

Applies to: SQL Server

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 isn't required that the database has any memory-optimized objects at the time of binding. In the absence of memory-optimized objects, there's no memory taken from the resource pool. This binding will be used by Resource Governor to manage memory allocated by In-Memory OLTP allocators.

If there's 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.

Transact-SQL syntax conventions

Syntax

sys.sp_xtp_bind_db_resource_pool
    [ @database_name = ] 'database_name'
    , [ @resource_pool_name = ] 'resource_pool_name'
[ ; ]

Arguments

[ @database_name = ] 'database_name'

The name of an existing In-Memory OLTP enabled database. @database_name is sysname.

[ @resource_pool_name = ] 'resource_pool_name'

The name of an existing resource pool. @resource_pool_name is sysname.

Messages

When an error occurs sp_xtp_bind_db_resource_pool returns one of these messages.

Database doesn't exist

@database_name must refer to an existing database. If there's 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 can't 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 doesn't exist

The resource pool identified by @resource_pool_name must exist prior to executing sp_xtp_bind_db_resource_pool. If there's 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 isn't 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 Server error log.

A resource binding has been successfully created between the database with ID %d and the resource pool with ID %d.

Examples

A. The following code example binds the database Hekaton_DB to the resource pool Pool_Hekaton.

sys.sp_xtp_bind_db_resource_pool N'Hekaton_DB', N'Pool_Hekaton';

The binding takes effect the next time the database is brought online.

B. This expanded version of the previous example includes some extra checks. Execute the following Transact-SQL in SQL Server Management Studio:

DECLARE @resourcePool SYSNAME = N'Pool_Hekaton';
DECLARE @database SYSNAME = N'Hekaton_DB';

-- Check whether resource pool exists
IF NOT EXISTS (
        SELECT *
        FROM sys.resource_governor_resource_pools
        WHERE name = @resourcePool
        )
BEGIN
    SELECT N'Resource pool "' + @resourcePool + N'" does not exist or resource governor has not been reconfigured.';
END
-- Check whether database is already bound to a resource pool
ELSE IF EXISTS (
        SELECT p.name
        FROM sys.databases d
        INNER JOIN sys.resource_governor_resource_pools p
            ON d.resource_pool_id = p.pool_id
        WHERE d.name = @database
        )
BEGIN
    SELECT N'Database "' + @database + N'" is currently bound to resource pool "' + @resourcePool + N'". A database must be unbound before creating a new binding.';
END
-- Bind resource pool to database.
ELSE
BEGIN
    EXEC sp_xtp_bind_db_resource_pool @database,
        @resourcePool;
END

Requirements

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.

Related content