Published on

October 25, 2016

How to Fix the Error “Property Size is Not Available for Database”

As a freelance SQL Server professional, I encounter various issues on a daily basis. It’s a constant learning process, and I enjoy sharing my knowledge with others. Today, I want to discuss a common error that you may come across when working with SQL Server: “Property Size is Not Available for Database.”

Recently, one of my clients reported that they were experiencing errors when trying to open the properties of the tempdb database. The exact error message was: “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)”

Upon researching this error, I found that many posts online pointed to the database owner as a potential cause. To investigate further, I ran the following command: sp_changedbowner 'sa'. However, since it was the tempdb database, I received the following message: “Msg 15109, Level 16, State 1, Line 1 Cannot change the owner of the master, model, tempdb, or distribution database.”

While troubleshooting, I noticed that it took some time to open the property window for the tempdb database. To dig deeper, we examined the sys.dm_exec_requests view and discovered a wait for PAGEIOLATCH on the tempdb database. Additionally, we checked the event viewer and found disk-related errors for the drive that contained the tempdb database.

The solution to this error depends on the specific cause, but here are a couple of common workarounds:

  1. Check the database ownership: Ensure that the owner is set correctly for the database. If it is not, you can use the sp_changedbowner command to change it.
  2. Check the health of the database: Run the DBCC CHECKDB command on the affected database to identify any potential issues. In some cases, corruption or other problems within the database can cause this error.

In my client’s case, the issue was related to the storage holding the tempdb files. The Windows team confirmed that there was corruption in the storage, which prevented us from reading or writing to the tempdb database. To resolve this, we stopped the SQL Server, deleted the tempdb files, and then restarted the SQL Server. As expected, the tempdb files were recreated. Finally, we moved the files to a new drive and replaced the faulty drive. Fortunately, since the client had a separate drive dedicated to tempdb, no other databases were affected.

Remember, troubleshooting SQL Server errors requires a systematic approach. Start by checking the database ownership and then investigate the health of the database. If necessary, involve the appropriate teams to address any underlying hardware or storage issues.

I hope this article helps you understand how to fix the error “Property Size is Not Available for Database.” If you have any questions or need further assistance, feel free to leave a comment below.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.