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

by | Feb 15, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

 


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. 


 


Backup.PNG


 


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!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment