Lesson Learned #162: Cannot show requested dialog obtaining properties database in Managed Instance

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I saw this error message when our customer is trying to obtain the properties of a database. This issue sometimes happened when SQL Server Management Studio is trying to obtain the last backup date done in Azure SQL Managed Instance.

For example, using SQL Server Profiler or Extended Event Profiler I was able to find the TSQL that SQL Server Management Studio is running, obtaining the same error message.

exec sp_executesql N' 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 = @_msparam_0 and db_id(database_name) = dtb.database_id) AS [LastBackupDate] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_1) drop table #tempbackup ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'D',@_msparam_1=N'database name'

In this situation, as the backup system that Azure SQL Managed Instance is different than other ones, in order to fix this issue, basically, you need to run the following command:

msdb..sp_delete_database_backuphistory '<database_name>'

After it, you are going to be able to obtain the properties of the database.

Enjoy!

Leave a Reply Cancel reply