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

SQL
1 Comment

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.

SQL SERVER - Cannot Show Requested Dialog - Property Size is Not Available for Database tempdb-er-01

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)

SQL Error Messages, SQL Server, SQL Server Management Studio
Previous Post
SQL SERVER – Unable to Attach Database – File Activation Failure – The Log Cannot be Rebuilt
Next Post
SQL SERVER – Fix Error Msg 10794, Level 16 – The operation ‘CREATE INDEX’ is not supported with memory optimized tables.

Related Posts

1 Comment. Leave new

  • Stephan Neethling
    January 31, 2019 8:22 pm

    I also got this message while rebuilding a clustered columnstore index on a user database. After the rebuild of the index the properties of the database was fully accessible again.

    Reply

Leave a Reply