Unable to open database properties window in SSMS

Question

I am using Azure SQL MI. Using SSMS, I backed up an SQL db to an Azure container. After that I am unable to open the Properties windows of that database using SSMS. When I right click on the database and click on Properties menu, I get the following error. I see the similar error online here but those links have different cause for the error.

Question: What could be cause of this error and how can we fix it?

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)

Answer 1

I ran into the same problem. The issue is in the msdb database, the backupset table has multiple entries for the same database, when the query SSMS is running only expects one. Run this query to find the duplicates.

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

You'll need to delete the backup history for the duplicates, which are created when you take a backup, as opposed to the ones SQL MI creates. The MI generated backups will have database_names that look like GUIDs. Do not delete them! Run this code to get rid of yours.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'your_db_name'

That will cure the problem.

Answer 2

...those links have different cause for the error.

The links you’ve found have the correct cause, it’s just hidden from you.

SSMS executes queries in the background to fill in the data you see in the Object Explorer (among other things). Sometimes these queries have bugs in them.

Try upgrading to the latest version of SSMS. If the issue still occurs, you can run a profiler trace against yourself when reproducing the issue. See Azure SQL Managed Instance frequently asked questions (FAQ) - Can I use SQL Profiler for performance tracking?

If you care to do so, I would encourage you to capture the underlying bugged out SMO query and report it with the repro steps as a bug at aka.ms/sqlfeedback