Cannot open the properties tab in SSMS for Managed Instance Database

Question

I am using Azure Managed Instance for some migration tasks. I have multiple databases in that all are working fine. But when I try to open the properties for a database named GCDCalculation, I am getting the following error:-

And the whole error is:-

 
Cannot show requested dialog.
 
===================================
 
Cannot show requested dialog. (SqlMgmt)
 
------------------------------
Program Location:
 
   at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc)
   at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)
   at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
   at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolMenuItemHelper.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
   at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()
 
===================================
 
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
 
------------------------------
For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-512-database-engine-error
 
------------------------------
Server Name: my-project-database.database.windows.net, 3342
Error Number: 512
Severity: 16
State: 1
Line Number: 7
 

------------------------------
Program Location:
 
   at Microsoft.SqlServer.Management.SqlManagerUI.DBPropGeneralData.InitProp()
   at Microsoft.SqlServer.Management.SqlManagerUI.DBPropGeneralData..ctor(CDataContainer context)
   at Microsoft.SqlServer.Management.SqlManagerUI.DBPropGeneral..ctor(CDataContainer dataContainer, DatabasePrototype prototype)
   at Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet.Init(CDataContainer dataContainer)
   at Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet..ctor(CDataContainer context)

All other Databases are working fine.

Added to this, I want to know that, is there specific query is triggered in the backend when we try to open the properties tab (Or any other tab).

Answer 1

Finally, one I can answer. Kollira is on the right track with this, the reason you get the error is that backupset contains records linking more than one database to the same ID. This happens because the DB was probably brought into SQL MI via the migration tool which creates the backupset with a GUID instead of the database name. When you do your own copy-only backup to URL, it creates a new backupset with the real name. This is not an optimised query to prove it but here goes.

select  db_id(database_name),database_name, [type], max(backup_finish_date) as latest from msdb..backupset where ([type] = 'D' or [type] = 'L' or [type]='I') and db_id(database_name) = (select database_id from sys.databases where name = 'Your_Database') group by database_name, [type]

Then we can see there is more than one DB listed, you can probably see the wrong one as it was created just when you backed up. So with this list of probably 2 DBs

Select * from  msdb..backupset where database_name in('DB1', 'DB2')

We can see conflicting backupsets. In my case the one with the real name was actually the new one that I wanted to remove, the other was a GUID I assume from my migration. So I just deleted the backupset as there were no backups associated. So simply:

Delete from msdb.dbo.backupset where  database_name = 'TheDatabaseName'

Boom, now properties rendered, and the original backup chain was still correct. I am not a SQL pro, so use at your own risk. Also, since this is no longer a Managed instance unique problem, maybe a SQL pro can advise a better solution.

Answer 2

The problem is with backups. Run this query and update the database name to the one which is latest.

create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime)
insert into #tempbackup 
select database_name, [type], max(backup_finish_date) from msdb..backupset where [type] = 'D' or [type] = 'L' or [type]='I' group by database_name, [type]

SELECT
(select backup_finish_date from #tempbackup where type = 'D' and db_id(database_name) = dtb.database_id)

AS [LastBackupDate]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name='Your Database Name')

drop table #tempbackup