Obtain an Exclusive Lock to Rename a SQL Server Database

~ Rick Bishop

Tested on: SQL Server 2016 Developer Edition
Accurate as of: January, 2018

Use this script to rename a database when the SQL Server database could not be exclusively locked to perform the operation.

-- First, set the database to single user mode

ALTER DATABASE CodeSnippets
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Now we will try to rename the database

ALTER DATABASE CodeSnippets MODIFY NAME = CodeSnippets_Staging


-- Finally we will set the database back to Multiuser mode


ALTER DATABASE CodeSnippets_Staging
SET MULTI_USER WITH ROLLBACK IMMEDIATE
 
 
--- Rick's Code Snippet Archive 
--- Snippet #E081D5E6A4BB6F741D26FBF8C2936E37 (TSQL) 
--- For the latest version of this code or to post a question or comment about it, visit:  
--- http://www.SevenDaysOfSchema.com/tsql-examples/obtain-an-exclusive-lock-to-rename-a-database/

Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.

In this Script

ALTER DATABASE     ALTER DATABASE     ALTER DATABASE     ALTER DATABASE    

ALTER DATABASE (Azure SQL Data Warehouse)

Modifies the name, maximum size, or service objective for a database.

Permissions: Requires these permissions:

– Server-level principal login (the one created by the provisioning process), or

– Member of the `dbmanager` database role.

The owner of the database cannot alter the database unless the owner is a member of the `dbmanager` role.

ALTER DATABASE database_name  

  MODIFY NAME = new_database_name  
| MODIFY ( &lgt;edition_option> [, ... n] )  
  
&lgt;edition_option> ::=   
      MAXSIZE = { 
            250 | 500 | 750 | 1024 | 5120 | 10240 | 20480 
          | 30720 | 40960 | 51200 | 61440 | 71680 | 81920 
          | 92160 | 102400 | 153600 | 204800 | 245760 
      } GB  
      | SERVICE_OBJECTIVE = { 
            'DW100' | 'DW200' | 'DW300' | 'DW400' | 'DW500' 
          | 'DW600' | 'DW1000' | 'DW1200' | 'DW1500' | 'DW2000' 
          | 'DW3000' | 'DW6000' | 'DW1000c' | 'DW1500c' | 'DW2000c' 
          | 'DW2500c' | 'DW3000c' | 'DW5000c' | 'DW6000c' | 'DW7500c' 
          | 'DW10000c' | 'DW15000c' | 'DW30000c'
      }


/* 

 A. Change the name of the database

*/

  
ALTER DATABASE AdventureWorks2012  
MODIFY NAME = Northwind;  





/* 

 B. Change max size for the database

*/

  
ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB );  





/* 

 C. Change the performance level

*/

  
ALTER DATABASE dw1 MODIFY ( SERVICE_OBJECTIVE= 'DW1200' );  





/* 

 D. Change the max size and the performance level

*/

  
ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB, SERVICE_OBJECTIVE= 'DW1200' );

Related Topics:   CREATE DATABASE (Azure SQL Data Warehouse)

ALTER Statements:   ALTER APPLICATION ROLE   ALTER ASSEMBLY   ALTER ASYMMETRIC KEY   ALTER AUTHORIZATION   ALTER AVAILABILITY GROUP   ALTER BROKER PRIORITY   ALTER CERTIFICATE   ALTER COLUMN ENCRYPTION KEY   ALTER CREDENTIAL   ALTER CRYPTOGRAPHIC PROVIDER   AUDIT SPECIFICATION   ENCRYPTION KEY   SCOPED CONFIGURATION   SCOPED CREDENTIAL   SET HADR   ALTER ENDPOINT   ALTER EVENT SESSION

TOP

ALTER DATABASE (Transact-SQL)

Modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Database snapshots cannot be modified. To modify database options associated with replication, use sp_replicationdboption.

Because of its length, the ALTER DATABASE syntax is separated into the following topics:

ALTER DATABASE
The current topic provides the syntax for changing the name and the collation of a database.

ALTER DATABASE File and Filegroup Options
Provides the syntax for adding and removing files and filegroups from a database, and for changing the attributes of the files and filegroups.

ALTER DATABASE SET Options
Provides the syntax for changing the attributes of a database by using the SET options of ALTER DATABASE.

ALTER DATABASE Database Mirroring
Provides the syntax for the SET options of ALTER DATABASE that are related to database mirroring.

ALTER DATABASE SET HADR
Provides the syntax for the Always On availability groups options of ALTER DATABASE for configuring a secondary database on a secondary replica of an Always On availability group.

ALTER DATABASE Compatibility Level
Provides the syntax for the SET options of ALTER DATABASE that are related to database compatibility levels.

For Azure SQL Database, see ALTER DATABASE
For Azure SQL Data Warehouse, see ALTER DATABASE.
For Parallel Data Warehouse, see ALTER DATABASE.

Permissions: Requires ALTER permission on the database.

-- SQL Server Syntax  
ALTER DATABASE { database_name  | CURRENT }  
{  
    MODIFY NAME = new_database_name   
  | COLLATE collation_name  
  | &lgt;file_and_filegroup_options>  
  | &lgt;set_database_options>  
}  
[;]  
  
&lgt;file_and_filegroup_options >::=  
  &lgt;add_or_modify_files>::=  
  &lgt;filespec>::=   
  &lgt;add_or_modify_filegroups>::=  
  &lgt;filegroup_updatability_option>::=  
  
&lgt;set_database_options>::=  
  &lgt;optionspec>::=   
  &lgt;auto_option> ::=   
  &lgt;change_tracking_option> ::=  
  &lgt;cursor_option> ::=   
  &lgt;database_mirroring_option> ::=   
  &lgt;date_correlation_optimization_option> ::=  
  &lgt;db_encryption_option> ::=  
  &lgt;db_state_option> ::=  
  &lgt;db_update_option> ::=  
  &lgt;db_user_access_option> ::=  &lgt;delayed_durability_option> ::=  &lgt;external_access_option> ::=  
  &lgt;FILESTREAM_options> ::=  
  &lgt;HADR_options> ::=    
  &lgt;parameterization_option> ::=  
  &lgt;query_store_options> ::=  
  &lgt;recovery_option> ::=   
  &lgt;service_broker_option> ::=  
  &lgt;snapshot_option> ::=  
  &lgt;sql_option> ::=   
  &lgt;termination> ::=


/* 

 A. Changing the name of a database

*/

  
USE master;  
GO  
ALTER DATABASE AdventureWorks2012  
Modify Name = Northwind ;  
GO  





/* 

 B. Changing the collation of a database

*/

  
USE master;  
GO  
  
CREATE DATABASE testdb  
COLLATE SQL_Latin1_General_CP1_CI_AS ;  
GO  
  
ALTER DATABASE testDB  
COLLATE French_CI_AI ;  
GO

Related Topics:   CREATE DATABASE   DATABASEPROPERTYEX   DROP DATABASE   EVENTDATA   SET TRANSACTION ISOLATION LEVEL   sp_configure   sp_spaceused   sys.data_spaces   sys.database_files   sys.database_mirroring_witnesses   sys.databases   sys.filegroups   sys.master_files   System Databases

ALTER Statements:   ALTER APPLICATION ROLE   ALTER ASSEMBLY   ALTER ASYMMETRIC KEY   ALTER AUTHORIZATION   ALTER AVAILABILITY GROUP   ALTER BROKER PRIORITY   ALTER CERTIFICATE   ALTER COLUMN ENCRYPTION KEY   ALTER CREDENTIAL   ALTER CRYPTOGRAPHIC PROVIDER   AUDIT SPECIFICATION   ENCRYPTION KEY   SCOPED CONFIGURATION   SCOPED CREDENTIAL   SET HADR   ALTER ENDPOINT   ALTER EVENT SESSION

TOP

ALTER DATABASE (Parallel Data Warehouse)

Modifies the maximum database size options for replicated tables, distributed tables, and the transaction log in Parallel Data Warehouse. Use this statement to manage disk space allocations for a database as it grows or shrinks in size.

!Topic link icon

Permissions: Requires the ALTER permission on the database.

-- Parallel Data Warehouse  
ALTER DATABASE database_name    
    SET ( &lgt;set_database_options>   | &lgt;db_encryption_option> )  
[;]  
  
&lgt;set_database_options> ::=   
{  
    AUTOGROW = { ON | OFF }  
    | REPLICATED_SIZE = size [GB]  
    | DISTRIBUTED_SIZE = size [GB]  
    | LOG_SIZE = size [GB]  
}  
  
&lgt;db_encryption_option> ::=  
    ENCRYPTION { ON | OFF }


/* 

 Determining Encryption Progress

*/

  
WITH  
database_dek AS (  
    SELECT ISNULL(db_map.database_id, dek.database_id) AS database_id,  
        dek.encryption_state, dek.percent_complete,  
        dek.key_algorithm, dek.key_length, dek.encryptor_thumbprint,  
        type  
    FROM sys.dm_pdw_nodes_database_encryption_keys AS dek  
    INNER JOIN sys.pdw_nodes_pdw_physical_databases AS node_db_map  
        ON dek.database_id = node_db_map.database_id   
        AND dek.pdw_node_id = node_db_map.pdw_node_id  
    LEFT JOIN sys.pdw_database_mappings AS db_map  
        ON node_db_map .physical_name = db_map.physical_name  
    INNER JOIN sys.dm_pdw_nodes nodes  
        ON nodes.pdw_node_id = dek.pdw_node_id  
    WHERE dek.encryptor_thumbprint <> 0x  
),  
dek_percent_complete AS (  
    SELECT database_dek.database_id, AVG(database_dek.percent_complete) AS percent_complete  
    FROM database_dek  
    WHERE type = 'COMPUTE'  
    GROUP BY database_dek.database_id  
)  
SELECT DB_NAME( database_dek.database_id ) AS name,  
    database_dek.database_id,  
    ISNULL(  
       (SELECT TOP 1 dek_encryption_state.encryption_state  
        FROM database_dek AS dek_encryption_state  
        WHERE dek_encryption_state.database_id = database_dek.database_id  
        ORDER BY (CASE encryption_state  
            WHEN 3 THEN -1  
            ELSE encryption_state  
            END) DESC), 0)  
        AS encryption_state,  
dek_percent_complete.percent_complete,  
database_dek.key_algorithm, database_dek.key_length, database_dek.encryptor_thumbprint  
FROM database_dek  
INNER JOIN dek_percent_complete   
    ON dek_percent_complete.database_id = database_dek.database_id  
WHERE type = 'CONTROL';  





/* 

 A. Altering the AUTOGROW setting

*/

  
ALTER DATABASE CustomerSales  
    SET ( AUTOGROW = ON );  





/* 

 B. Altering the maximum storage for replicated tables

*/

  
ALTER DATABASE CustomerSales  
    SET ( REPLICATED_SIZE = 1 GB );  





/* 

 C. Altering the maximum storage for distributed tables

*/

  
ALTER DATABASE CustomerSales  
    SET ( DISTRIBUTED_SIZE = 1000 GB );  





/* 

 D. Altering the maximum storage for the transaction log

*/

  
ALTER DATABASE CustomerSales  
    SET ( LOG_SIZE = 10 GB );

Related Topics:   CREATE DATABASE   DROP DATABASE

ALTER Statements:   ALTER APPLICATION ROLE   ALTER ASSEMBLY   ALTER ASYMMETRIC KEY   ALTER AUTHORIZATION   ALTER AVAILABILITY GROUP   ALTER BROKER PRIORITY   ALTER CERTIFICATE   ALTER COLUMN ENCRYPTION KEY   ALTER CREDENTIAL   ALTER CRYPTOGRAPHIC PROVIDER   AUDIT SPECIFICATION   ENCRYPTION KEY   SCOPED CONFIGURATION   SCOPED CREDENTIAL   SET HADR   ALTER ENDPOINT   ALTER EVENT SESSION

TOP

ALTER DATABASE (Azure SQL Database)

Modifies a Azure SQL Database. Changes the name of a database, the edition and service objective of a database, join an elastic pool, and sets database options.

Permissions: Only the server-level principal login (created by the provisioning process) or members of the `dbmanager` database role can alter a database.

IMPORTANT: The owner of the database cannot alter the database unless they are a member of the `dbmanager` role.

-- Azure SQL Database Syntax  
ALTER DATABASE { database_name }  
{  
    MODIFY NAME = new_database_name  
  | MODIFY ( &lgt;edition_options> [, ... n] )   
  | SET { &lgt;option_spec> [ ,... n ] }   
  | ADD SECONDARY ON SERVER &lgt;partner_server_name>  
      [WITH ( &lgt;add-secondary-option>::= [, ... n] ) ]  
  | REMOVE SECONDARY ON SERVER &lgt;partner_server_name>  
  | FAILOVER  
  | FORCE_FAILOVER_ALLOW_DATA_LOSS  
}  
[;] 

&lgt;edition_options> ::=   
{  

      MAXSIZE = { 100 MB | 250 MB | 500 MB | 1 … 1024 … 4096 GB }    
    | EDITION = { 'basic' | 'standard' | 'premium' }   
    | SERVICE_OBJECTIVE = 
                 {  &lgt;service-objective>
                 | { ELASTIC_POOL (name = &lgt;elastic_pool_name>) }   
                 }   
}  

&lgt;add-secondary-option> ::=  
   {  
      ALLOW_CONNECTIONS = { ALL | NO }  
     | SERVICE_OBJECTIVE =   
                 {  &lgt;service-objective> 
                 | { ELASTIC_POOL ( name = &lgt;elastic_pool_name>) }   
                 }   
   }  

&lgt;service-objective> ::=  { 'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |
                 | 'P1' | 'P2' | 'P4'| 'P6' | 'P11'  | 'P15' }

  
  

-- SET OPTIONS AVAILABLE FOR SQL Database  
-- Full descriptions of the set options are available in the topic   
-- ALTER DATABASE SET Options. The supported syntax is listed here.  

&lgt;option_spec> ::=   
{  
    &lgt;auto_option>   
  | &lgt;change_tracking_option> 
  | &lgt;cursor_option>   
  | &lgt;db_encryption_option>  
  | &lgt;db_update_option>   
  | &lgt;db_user_access_option>   
  | &lgt;delayed_durability_option>  
  | &lgt;parameterization_option>  
  | &lgt;query_store_options>  
  | &lgt;snapshot_option>  
  | &lgt;sql_option>   
  | &lgt;target_recovery_time_option>   
  | &lgt;termination>  
  | &lgt;temporal_history_retention>  
}  
  
&lgt;auto_option> ::=   
{  
    AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] }   
  | AUTO_SHRINK { ON | OFF }   
  | AUTO_UPDATE_STATISTICS { ON | OFF }   
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }  
}  

&lgt;change_tracking_option> ::=  
{  
  CHANGE_TRACKING   
   {   
       = OFF  
     | = ON [ ( &lgt;change_tracking_option_list > [,...n] ) ]   
     | ( &lgt;change_tracking_option_list> [,...n] )  
   }  
}  

   &lgt;change_tracking_option_list> ::=  
   {  
       AUTO_CLEANUP = { ON | OFF }   
     | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }  
   }  

&lgt;cursor_option> ::=   
{  
    CURSOR_CLOSE_ON_COMMIT { ON | OFF }   
}  
  
&lgt;db_encryption_option> ::=  
    ENCRYPTION { ON | OFF }  
  
&lgt;db_update_option> ::=  
    { READ_ONLY | READ_WRITE }  
  
&lgt;db_user_access_option> ::=  
    { RESTRICTED_USER | MULTI_USER }  
  
&lgt;delayed_durability_option> ::=    DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }  
  
&lgt;parameterization_option> ::=  
    PARAMETERIZATION { SIMPLE | FORCED }  
  
&lgt;query_store_options> ::=  
{  
    QUERY_STORE   
    {  
          = OFF   
        | = ON [ ( &lgt;query_store_option_list> [,... n] ) ]  
        | ( &lgt; query_store_option_list> [,... n] )  
        | CLEAR [ ALL ]  
    }  
}   
  
&lgt;query_store_option_list> ::=  
{  
      OPERATION_MODE = { READ_WRITE | READ_ONLY }   
    | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )  
    | DATA_FLUSH_INTERVAL_SECONDS = number   
    | MAX_STORAGE_SIZE_MB = number   
    | INTERVAL_LENGTH_MINUTES = number   
    | SIZE_BASED_CLEANUP_MODE = [ AUTO | OFF ]  
    | QUERY_CAPTURE_MODE = [ ALL | AUTO | NONE ]  
    | MAX_PLANS_PER_QUERY = number  
}  
  
&lgt;snapshot_option> ::=  
{  
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }  
  | READ_COMMITTED_SNAPSHOT {ON | OFF }  
  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT {ON | OFF }  
}  
&lgt;sql_option> ::=   
{  
    ANSI_NULL_DEFAULT { ON | OFF }   
  | ANSI_NULLS { ON | OFF }   
  | ANSI_PADDING { ON | OFF }   
  | ANSI_WARNINGS { ON | OFF }   
  | ARITHABORT { ON | OFF }   
  | COMPATIBILITY_LEVEL = { 100 | 110 | 120 | 130 | 140 }  
  | CONCAT_NULL_YIELDS_NULL { ON | OFF }   
  | NUMERIC_ROUNDABORT { ON | OFF }   
  | QUOTED_IDENTIFIER { ON | OFF }   
  | RECURSIVE_TRIGGERS { ON | OFF }   
}  
  
&lgt;termination>  ::=   
{  
    ROLLBACK AFTER integer [ SECONDS ]   
  | ROLLBACK IMMEDIATE   
  | NO_WAIT  
}  

&lgt;temporal_history_retention>  ::=  TEMPORAL_HISTORY_RETENTION { ON | OFF }
  
  
 For full descriptions of the set options, see &lgt;a target="rick" href="https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/t-sql/statements/alter-database-transact-sql-set-options.md">ALTER DATABASE SET Options&lgt;/a> and &lgt;a target="rick" href="https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/t-sql/statements/alter-database-transact-sql-compatibility-level.md">ALTER DATABASE Compatibility Level&lgt;/a>.


/* 

 Arguments

*/

  
ALTER DATABASE db1  
    MODIFY Name = db2 ;  





/* 

 A. Check the edition options and change them:

*/


SELECT Edition = DATABASEPROPERTYEX('db1', 'EDITION'),
        ServiceObjective = DATABASEPROPERTYEX('db1', 'ServiceObjective'),
        MaxSizeInBytes =  DATABASEPROPERTYEX('db1', 'MaxSizeInBytes');

ALTER DATABASE [db1] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');





/* 

 B. Moving a database to a different elastic pool

*/

  
ALTER DATABASE db1   
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = pool1 ) ) ;  





/* 

 C. Add a Geo-Replication Secondary

*/

  
ALTER DATABASE db1   
ADD SECONDARY ON SERVER secondaryserver   
WITH ( ALLOW_CONNECTIONS = ALL )  





/* 

 D. Remove a Geo-Replication Secondary

*/

  
ALTER DATABASE db1   
REMOVE SECONDARY ON SERVER testsecondaryserver   





/* 

 E. Failover to a Geo-Replication Secondary

*/

  
ALTER DATABASE db1 FAILOVER

Related Topics:   CREATE DATABASE   DATABASEPROPERTYEX   DROP DATABASE   EVENTDATA   SET TRANSACTION ISOLATION LEVEL   sp_configure   sp_spaceused   sys.data_spaces   sys.database_files   sys.database_mirroring_witnesses   sys.databases   sys.filegroups   sys.master_files   System Databases

ALTER Statements:   ALTER APPLICATION ROLE   ALTER ASSEMBLY   ALTER ASYMMETRIC KEY   ALTER AUTHORIZATION   ALTER AVAILABILITY GROUP   ALTER BROKER PRIORITY   ALTER CERTIFICATE   ALTER COLUMN ENCRYPTION KEY   ALTER CREDENTIAL   ALTER CRYPTOGRAPHIC PROVIDER   AUDIT SPECIFICATION   ENCRYPTION KEY   SCOPED CONFIGURATION   SCOPED CREDENTIAL   SET HADR   ALTER ENDPOINT   ALTER EVENT SESSION

About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.

Published by Rick Bishop

Question or comment?