14

I recently restored a database to the same instance it was backed up from (SQL Server 2008 R2 Enterprise) and found that I couldn't access the database properties.

I have done the following:

  • Checked the database owner was set correctly using sp_helpdb.
  • Changed the database owner to sa. Not a fix.
  • Changed the database owner back to my sysadmin user. Not a fix.
  • Issued DBCC updateusage against the affected database. Not a fix.
  • Run DBCC CheckDB on a restored copy to another instance. No corruption found. The restored copy (from the same backup file) did not throw any errors when accessing the database properties window.

Can anyone help?

The error message I get when trying to view properties is:

Cannot show requested dialog. (SqlMgmt)
Property Size is not available for Database '[DBNAME]'.
This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

I am a sysadmin on this instance.

Update: As suggested I created a new user, made it sysadmin and changed the database owner to it. Not a fix unfortunately. I will see if a profiler trace yields anything useful.

Update: Aaron - the original database was renamed and taken offline but is still on that instance. The backup of that database was then restored using the original name. The filenames of the new database files are different from the original as they live in the same folder as the original mdf/ldf. The restored db is currently driving our critical apps as normal.

  • @Shanky I'm using SSMS 2014 and have attempted this from multiple clients/servers. – Peter Sep 17 '15 at 14:02
  • Perhaps also run a profiler trace against the DB where it does work and see what's different there too with the one where it does work and does not work -- something has to be different? – Pimp Juice IT Sep 19 '15 at 18:21
  • I very recently had this issue after restoring a large database. I'm sorry to say it cleared itself though. Might be a silly question, but have you checked the errorlog for anything untoward? – dwjv Sep 22 '15 at 15:34
8

I have resolved this by restarting the SQL Server service, unfortunately/fortunately.

My initial idea was to detach/attach the database. Not that I thought this would actually fix the problem, I just read that in a forum somewhere. This had no effect.

I regrettably did not do what PJ Mahoney suggested re: running a trace. I hope I see this issue again so I can find out if a trace reveals anything. Thanks for the suggestion.

At least answering my own question means I don't have to see another suggestion about changing the database owner ;P

Thanks

5

I had this same error when trying to modify the file growth properties of the log file of a production database. I tried using SSMS and had the same error:

Property size is not available for XXX database.

I used T-SQL to modify the database log file instead.

USE [master]
GO
ALTER DATABASE [MyDatabase] MODIFY FILE ( NAME = N'MyDatabase_log', FILEGROWTH = 524288KB )
GO

After the command completed I was able to open the database properties in SSMS. I hope this works for others.

  • interesting! Comment here if that worked for you – Peter Feb 23 '16 at 19:57
  • working fine and right? – Kiquenet Apr 25 at 14:24
1

This can happen on very busy databases or even if the database is not a busy one in general, but at the moment it's executing a statement that is rapidly changing the log file size. For instance, if you try to delete a very large number of rows (millions) from a table, the engine will have to log every row in for rollback purposes, which will force the log file to grow rapidly. While this is in progress, if you try to open the database properties in SSMS, you will see the error message "property size is not available.."

Another way to reproduce it is as follows: take a very large database (100+ million rows), and execute an ALTER TABLE statement on it (e.g. convert a char column to a varchar). See your log file blow up in hunderds of megs or even gigabytes per second. Again, while this is happening, you SSMS will not be able to give you the database size because it's changing too fast to be accurate.

Needless to say, please don't try this on live servers. :)

1

Just came across this and from a trace, apparently this got blocked by an INDEX REBUILD operation that was running

My specific error was

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

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

It must have a very low timeout value, but maybe you can check if you have anything getting blocked when you try to access the properties

  • Interesting. I’ll test that. – Peter Nov 2 '18 at 15:01
0

I get this error message during deployment of a SQL Server database tools solution against the database (on SQL Server 2008 R2). After the deployment finishes, the error is gone. It's a little nasty because neither is the DB in Single User mode or readonly (of corse not) or anything else indicating the reason.

protected by Max Vernon Mar 29 '16 at 15:48

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.