SQL SERVER – Cannot Show Requested Dialog – Property Size is Not Available for Database

Freelancing gives me a lot of opportunity to see issues which I have not seen earlier. Learning never stops for me and I love sharing what I learn every day. Let us learn about how to fix the error Property Size is Not Available for Database.

One of my clients reported that they are seeing errors opening tempdb database properties.

Property Size is not available for Database ‘[tempdb]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

When I searched for similar error on internet and many posts pointed about owner of the database. So, I ran below

sp_changedbowner 'sa'

But since it was tempdb database, I received below message.

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.

I noticed that when I open property, it takes some time. We looked into sys.dm_exec_requests and found that wait for PAGEIOLATCH on TempDB database. We checked the event viewer and there were disk related errors for the drive contains the TempDB database.

SOLUTION/WORKAROUND

If you are getting same error, then check database ownership first. If the owner is set correctly, check the health of the database by running DBCC CHEKDB on the database. In my case, it was the storage which was holding TempDB files. Windows team confirmed that storage was having corruption and we were not able to read/write to TempDB. We stopped SQL, deleted TempDB files and started SQL Server and as expected they were recreated. Later, we moved them to new drive and replaced the bad drive. Luckily this client was having separate drive for TempDB and nothing else was impacted.

Reference: Pinal Dave (https://blog.sqlauthority.com)