Database Options – Page 2

Property Owner is Not Available for Database…

Property Owner is Not Available for Database

One of the Most annoying error Messages(which really irritates a novice DBA) when you try to look at your Database Properties/Options from SQL Server Management Studio(SSMS). You’ll get something similar to the below screenshot when you right click on the database and choose properties.

Okay…So Let’s run our legacy sp_helpdb and see the result set.

Well, it says the owner of ‘Adventureworks’ database is “UNKNOWN”…Umm Interesting!! So what can we do to fix this issue?

Resolution: Assigning an owner for this database from T-SQL. Typically i prefer to go with ‘sa’ as Database Owner for all the databases. We can do this in either way mentioned below

Legacy(SQL 2000) way:

Use Adventureworks
GO
sp_changedbowner ‘sa’

New way starting SQL Server 2005:

ALTER AUTHORIZATION ON DATABASE::Adventureworks TO sa.

So what is the root cause for this strange behavior of database from SSMS? What happened exactly?? Well, consider a scenario where your coworker has created a database and he/she(Windows Account) is the current owner of the database(Just let’s assume). For some reason he/she left the organization and his/her Windows Account will be removed from the Active Directory by AD Administrator(and as a DBA you’ll drop his/her login from SQL Server). In other words his/her windows login is no longer a valid account.

Illustration:

I’ve created a windows user account called “baddba” and I’ve added him as a login in my SQL Server and made him the Database Owner for Adventureworks database as shown below.

Let’s me simulate a scenario where he/she leaves our company and our AD Admin removes his/her account and another DBA drops the login from SQL Server.(Actually i did both the steps behind the scenes).

Now let’s try to select database properties from SSMS….

Grrrrrrrrr…..same Error!!!

Conclusion: Try to avoid having individual Login as a database Owner. Definitely a very bad practice as justified in this post! Instead, make it ‘sa’ on all the databases. You can make use of Powershell to achieve this if you are really supporting tons and tons of databases and have no patience to deal with individual database(s) as i do:)

DMVs with Database Compatibility Level 80??…

Let me give a brief background on why i would like to discuss regarding this interesting and most often ignored topic of Dynamic Management Views(DMVs) and their relation with Database Compatibility Mode.

Note: When i say a DMV, i actually mean DMO(Dynamic Management Object) which Includes both DMV’s and DMF’s(Dynamic Management Functions). To avoid the confusion, people generalized and started calling both DMV’s and DMF’s with a single term called DMV and i love it:)

Why am i sharing this today?? Well, we(our Team) recently started a Small Internal Project something called as “Performance Tuning Initiative”. As you all know, a DMV can help a DBA than any other free tool(Sometimes a paid tool too) out there in market!! So…we started to Pull all the Cool information with proper benchmarks and started doing trend analysis and monitoring using few fabulous DMVs which are available to all of us.
Note: We’ve few Databases with Compatibility Mode 80 because of Vendor Restrictions even running on SQL Server 2005 SP4. So…as you imagined by this time, today in the morning myself and one of the DBA’s in my team had a discussion regarding how DMV’s behave if run against a database with Compatibility Level 80. Interestingly, he was in a misconception – “Most of the DMV’s are of no use if the DB Compatibility Level is 80” and i was arguing exactly the other way.- “Most(Almost every) of the DMV’s will have no issues even the Compatibility level is 80”

Quick Demo:
I’ve a Database “AdventureWorksDW” set to Compatibility Level 80 as you can see below.

Let’s run few typical DMV’s which we’ll be using on a regular basis against this database.

In the above Screenshot you can see it had no issues with the “AdventureWorksDW” Database.

Let’s see what happens if we run our sys.dm_db_index_physical_stats against this Database.

Ummm…it is screaming that there’s a syntax error. Actually it’s not. There’s a Simple trick to crack this thing.

Crack – Execute the DMV in context of any other database which has 90/100 compatibility mode. In my case I’m running on SQL Server 2008R2 and my master database has 100 Compatibility level.
Syntax 1 where we had issues:

USE AdventureWorksDW
GO
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO

Syntax 2 where we are having no issues:

use master
GO
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID(‘AdventureWorksDW’)
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO
The results are as shown below in the screenshot.

If you observe the syntax carefully, I’ve just changed the database context of execution and I’m passing my Database which is in Compatibility level 80 to the DB_ID() Function.
What’s the Reason behind this??
Very Very Important……
Because the physical structure(Version) of the database is not going to change when you change compatibility level.In other words you are not downgrading a Database at it’s underlying physical architectural level by just reducing the compatibility level to an older version. You are changing only few Queries execution behaviors! That’s all basically what you are achieving here by reducing compatibility level to 80 in this case. Once the Database is on SQL Server 2005 by any means(Create from Scratch/Restored/Detached-attached), that’s it…the Database Version is set to 611/612.
Note:
SQL Server Denali Database Version – 700
SQL Server 2008R2 Database Version – 661
SQL Server 2008 Database Version – 655
SQL Server 2005 Database Version – 611/612
SQL Server 2000 Database Version – 539.

That’s the reason why you can’t restore a Database from SQL Server 2005(2008) onto SQL Server 2000(2005) even though you changed the compatibility level to 80(90). SQL Server won’t allow to downgrade any database version. No exceptions here!! You’ve to choose the difficult path of moving object by object and data using SSIS!!

Let’s do a Quick Demo what I’m talking about:
SQL Server Database stores the Database version information in it’s Boot Page( Page 9 of 1st File in the database). I’ve to turn on traceflag 3604 to dump the boot page to our results pane. For More Information on this please see my previous blog post http://sql-buzz.blogspot.com/2011/06/checkdb-in-errorlogs-on-startup.html

In the above screenshot, you can see the database has 80 Compatibility level but the DB version is 661(SQL Server 2008R2).

Hope this information is useful….!

Database Status – Auto Close

Should we really care about Auto Close turned ON on your Database?
Ummm……I would say YES. It does matter to me a lot If It is turned ON for my Production Database. (I’m not saying that I’m Okay with turning ON this Setting on my Non-Prod Databases).

Before wrapping up my day in the Office i don’t know for some reason, I thought of doing a Basic scan of Databases on couple of servers which our team is supporting before leaving. Basically I was looking for “Recovery Model”, “Auto Shrink”,”Page_verify_Option”,”Auto Update Stats”. Well, i found few to fix and interestingly I saw one of the Databases showing something similar to below Screenshot as it’s current status.

hmmm….What does it mean?? Well, It’s basically saying that “Auto Close” is turned ON and since all the Connections are dropped It’ll show you the status as- “Normal/AutoClosed/Shutdown“. Once any session is opened on this database, the “Autoclosed” from above screenshot will disappear! It’s time to poke my fellow DBA why this has been enabled on his Database.

FYI If you query system catalogs for status, it simply says ONLINE even with DATABASEPROPERTYEX. what you’ve to look for is “is_auto_close_on” in sys.databases as shown below

So…Coming to the Point..Why you should concern about this is….SQL Server will be allocating and consuming few resources each and every time a database has been initialized for the first time. So if Auto Close is turned ON, your database will be Closed when the last session is terminated. When a user issues a new query or a new request hitting this Database, SQL Server has to basically Startup your Database again which leads to consuming those few resources again and this process repeats each time your DB is Auto Closed and a new connection begins….Hope i made it clear.

If you look into error logs, you’ll be seeing few instances where it says something similar to below which is an obvious and unnecessary Overhead on your SQL Server.