Sql-server – Property Size is not available for database

permissionssql serversql-server-2008-r2

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.

Best Answer

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

Related Question