Create a database

When you create a database on the Databases page of an ApsaraDB RDS for SQL Server instance, the error message Specified DB name already exists in the This instance. is returned. The error code is InvalidDBName.Duplicate.

A database that has the same name already exists on the instance. You cannot create a database whose name is the same as that of an existing database.

In addition, the Databases page of an ApsaraDB RDS for SQL Server instance displays only databases with the ONLINE status. Databases that are not in the ONLINE status (such as RESTORING, RECOVERING, or OFFLINE) are not displayed in the console. Therefore, you may mistakenly believe that the database does not exist, which can lead to creation failures.

1. Connect to the ApsaraDB RDS for SQL Server instance by using SSMS.

2. Execute the following SQL statement to query the non-system databases on the instance and their states:

   SELECT
       [name] AS DatabaseName,  -- The name of the database
       state_desc AS StateDescription,  -- The current state of the database, such as ONLINE, OFFLINE, or RECOVERING
       recovery_model_desc AS RecoveryModel  -- The recovery model of the database, such as SIMPLE or FULL
   FROM
       sys.databases
   WHERE
       database_id > 4; -- Filter out system databases

   The following sample result is returned. You can check whether a database that has the same name exists and view the current state of the database.

   DatabaseName    StateDescription    RecoveryModel
   jmdb            ONLINE              FULL
   jmdb200         RECOVERING          FULL

3. If a database that has the same name exists but is not in the ONLINE state, resolve the issue based on the following scenarios:

   • Scenario 1: You choose not to bring the database online when you restore backup data from OSS

     • Cause

       When you restore backup data from OSS to an ApsaraDB RDS for SQL Server instance, if you select the Do not bring the database online (for additional differential or log backups) option, the database stays in a non-ONLINE state.

       This option is located in the Migration Plan section in step 3. Import Data of the Import Guide, which appears after you click Migrate OSS Backup Data to RDS on the Backup and Restoration page in the left-side navigation pane of the RDS console.

     • Solution

       If you still need to upload incremental backup files, bring the database online on the Cloud Migration Records of Backup Data tab after the restoration is complete. If you no longer need to restore incremental data, you can directly bring the database online.

       In this case, on the Cloud Migration Records of Backup Data tab, the task status is Waiting, the task type is Do not bring the database online (for additional differential or log backups), and the task description is Waiting for online.

       If the database is no longer needed and must be re-created, you can bring the database online, delete the database, and then create the database again.

   • Scenario 2: The database enters the RECOVERING state because the log space is full

     • Cause

       The log space is full and logs cannot be written. As a result, the database enters the RECOVERING state. You can check the error logs of the instance to confirm the issue:

       3/16/2024 2:05:35 AM    spid128    During undoing of a logged operation in database 'db_logistics', an error occurred at log record ID (1:88854:14712:2). Typically, the specific failure is logged previously as an error in...
       3/16/2024 2:05:35 AM    spid128    Error: 3314, Severity: 21, State: 3.
       3/16/2024 2:05:35 AM    spid128    The transaction log for database 'xxx' is full due to 'LOG_BACKUP'.
       3/16/2024 2:05:35 AM    spid128    Error: 9002, Severity: 17, State: 2.
       3/16/2024 2:05:35 AM    spid128    The transaction log for database 'xxx' is full due to 'LOG_BACKUP'.

     • Solution

       • If the storage space of the instance is full and the log space cannot be extended, you must expand the storage capacity of the instance and then manually restart the instance. After the restart, the database continues the RECOVERING process. After the recovery is complete, the database returns to the ONLINE state.

       • If a growth limit is set for the log file of the database and the log space is full but the storage space of the instance is not full, you need only to increase the size limit of the log file. You do not need to restart the instance. To adjust the size limit of the log file, perform the following operations:

         In Object Explorer of SQL Server Management Studio (SSMS), right-click the database, such as jmdb, and select Properties.

         In the left-side navigation pane of the Database Properties dialog box, select the Files page. In the Database files table, find the log file whose File Type is LOG, and click the ... button in the Autogrowth / Maxsize column to adjust the automatic growth settings and maximum size of the log file.

         In the Change Autogrowth for jmdb_log dialog box, select Enable Autogrowth, set File Growth to In Megabytes and enter a growth value, such as 64, set Maximum File Size to Limited to (MB) and enter a maximum file size, such as 2,097,152, which indicates 2 TB, and then click OK.

       If the database is no longer needed and must be re-created, you can wait until the database enters the ONLINE state, delete the database, and then create the database again.

   • Scenario 3: The database is manually set to the OFFLINE state

     • Cause

       The database may be manually set to the OFFLINE state by an administrator.

     • Solution

       Use the sp_rds_set_db_online stored procedure to bring the database back to the ONLINE state:

       USE master;
       GO
       EXEC sp_rds_set_db_online 'Database name';
       GO

       If the database is no longer needed and must be re-created, you can wait until the database enters the ONLINE state, delete the database, and then create the database again.