Subquery returned more than 1 value when open Azure Managed Instance Database properties

[Guest Mohamed_Baioumy_MSFT]

Recently some customers faced the error below when trying to access SQL Managed Database properties (right-click on database and select properties), they got the following error:

 

 

 

 

 

 

TITLE: Microsoft SQL Server Management Studio

 

------------------------------

 

Cannot show requested dialog.

 

------------------------------

 

ADDITIONAL INFORMATION:

 

Cannot show requested dialog. (SqlMgmt)

 

------------------------------

 

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft SQL Server, Error: 512)

 

 

 

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-512-database-engine-error

 

------------------------------

 

BUTTONS:

 

OK

 

------------------------------

 

 

 

Investigation/Analysis:

 

The information for COPY_ONLY full backups initiated by user was stored in msdb.dbo.backupset table. Column database_name contained logical database name.

 

 

 

In order to have better automatic backup transparency we have recently enabled storing information about automatic backups into msdb.dbo.backupset table as well. Due to a defect in the code instead of logical database name we stored a unique identifier of the managed database. That caused the issue with SSMS as the internal query expects to have one of these two.

 

 

 

Mitigation

 

Mitigation is to clear backup history from MSDB using sp_delete_backuphistory.

 

 

 

EXEC msdb..sp_delete_backuphistory @oldest_date = '<mm/dd/yyyy>'

 

 

 

This command deletes the entries for backup sets older than the specified date.

 

 

 

To identify which backup set is causing the issue, you can run below query:

 

 

 

SELECT DB_ID(database_name) AS [db_id(database_name)], database_name , backup_start_date, machine_name

 

FROM msdb..backupset WHERE [type] = 'D' ORDER BY 1,2

 

 

 

The output from above query shows multiple entries for the same logical database name in msdb..backupset. You will need to delete the backup history for the duplicates based on database name using sp_delete_backuphistory

 

 

 

Do not delete entries that look like GUIDs. instead run this code to directly delete entries in backupset table based on database name:

 

 

 

EXEC msdb..sp_delete_database_backuphistory @database_name = '<database_name>'

 

Continue reading...