Property Size is not available for database

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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. :)

Answer 4

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

Answer 5

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.