How to rename MS SQL Server Database in SSMS

This article describes how to rename database in Microsoft SQL Server Management Studio.

Problem:

I wanted to rename one MS SQL database in MS SQL Management Studio via the context menu.

Renaming database via the context menu

After typing the new name and pressing Enter I got the following error message:

Error message during renaming the database
TITLE: Microsoft SQL Server Management Studio
------------------------------
Unable to rename TESTDB_1. (ObjectExplorer)
------------------------------
ADDITIONAL INFORMATION:
Rename failed for Database 'TESTDB'.  (Microsoft.SqlServer.Smo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)
------------------------------

Then I tried to rename with the following SQL statement:

ALTER DATABASE TESTDB
MODIFY NAME = TESTDB_1

In this case I got this error:

The database could not be exclusively locked to perform the operation.

Solution:

After some search on the Internet I found information, that I got this error, because the database is in multi-user mode and has some open connection.

Here is the solution which worked for me:

  1. At first need to change the database to single-user mode:

    ALTER DATABASE TESTDB
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    

  2. Then try to rename the database:

    ALTER DATABASE TESTDB
    MODIFY NAME = TESTDB_1
    

  3. Finally, set back the database to multi-user mode:

    ALTER DATABASE TESTDB_1
    SET MULTI_USER WITH ROLLBACK IMMEDIATE
    


6x faster WordPress Hosting6x faster WordPress Hosting

The Distributed Transaction doesn’t working

Problem:

System.Runtime.InteropServices.COMException (0x8004D00A): New transaction cannot enlist in the specified transaction coordinator.

Solution:

This error occurs when distributed transactions are not allowed over network.

By modifying the MSDTC security settings, you control how MSDTC communicates with remote computers over the network.

To access the MSDTC security configuration options follow these steps:

On Windows Server 2003 SP1 and Windows XP SP2:

  1. Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.
  2. Click to expand Component Services and click to expand Computers.
  3. Right-click My Computer, and click Properties.
  4. Click on the MSDTC tab of the My Computer Properties dialog and click on the Security Configuration button to display the Security Configuration dialog box.

On Windows Server 2008, Windows 7 and Windows 10:

  1. Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.
  2. Click to expand Component Services and click to expand Computers.
  3. Click to expand My Computer, click to expand Distributed Transaction Coordinator, right-click Local DTC, and click Properties.
  4. Click the Security tab of the Local DTC Properties dialog.
Local DTC Properties

Network DTC Access check box – Select this check box if you want to allow any network traffic for the Distributed Transaction Coordinator (DTC).

If this check box is not selected, the DTC will not flow any transactions to the network, and it will not accept any incoming traffic. Remote administration of this DTC will also be disabled.

Allow Inbound: Select this check box to allow a remote computer to flow transactions to the local computer. Typically, this option is needed on the computer that is hosting the DTC for a resource manager such as Microsoft SQL Server.

Allow Outbound: Select this check box to allow the local computer to flow transactions to a remote computer. Typically, this option is needed on the client computer, where the transaction is initiated.

Click OK.

Finally, restart the application which works with distributive transactions.


6x faster WordPress Hosting6x faster WordPress Hosting