Category: Generic Errors

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

 

Database ‘testDB’ is in Transition. Try the Statement later. Error:952

Have you ever been in a situation where your database is stuck in “Transition” yelling at you saying..

Database ‘Transient_Test’ is in transition. Try the statement later. (Microsoft SQL Server, Error: 952)

A Junior DBA’s first thought would be “Heard about ONLINE and OFFLINE, what is this TRANSIENT Status?? I’m having this Issue for the first time…OMG! Did i lost my Database?” Basically he starts pressing “PANIC BUTTON” on his/her Keyboard! which we never should as a DBA.(Trust me Guys, you’ll pay the penalty if you PANIC. Our Role as a DBA is to turn the PANIC Situation to a pleasant situation, not to supply oxygen to fire ;p

So..what does it really mean? Simple and straight forward thought which should strike on your mind is,
your Database is transitioning from Online State to Offline State, but for some reason it got stuck in middle. So SQL is saying that it’s trying to go Offline but it’s not able to!

Let’s simulate this scenario:
Step 1:
I’ve created a test database to play with and let’s call it as “transient_test”. I’ve created a test table to insert few records in an explicit transaction.
Table Structure:

/****** Object:  Table [dbo].[test]    Script Date: 07/14/2011 21:51:43 ******/
CREATE TABLE [dbo].[test](
[id] [int] NOT NULL,
[name] [char](10) NOT NULL,
[add] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
Step 2:
Begin an explicit transaction as shown below in the screenshot






Step3:
In another Session,try to take the Database offline. It won’t go Offline and it will remain there sitting “in Progress” for ever as shown below, because of the Open Transaction.(We’ve to either commit/Rollback and Close that session to take database offline successfully).





Step 4: Now let’s try to access the Database from either Object Explorer or try to Query anything in that database. It’s screaming saying as shown below

Volaaaaaa….That’s it Guys to simulate this. Pretty Simple huh!!

Now let’s assume that’s you are not aware of what happened and you got a call from Application team for immediate assistance.
Pretty Simple to investigate…Just issue “select * from sys.dm_exec_requests” to see what exactly are running currently or just legacy SP_WHO2 and investigate further.
Note: I’m showing a very old school technique using INPUTBUFFER and Ad-HOC individual DMV’s since i know there is no activity going on this server since it’s my test lab. In a real environment i suggest you to make use of combination of DMV’s at a single stretch which does all the magic for you. Search for “performance tuning SQL Server 2005/2008 Whitepaper from Microsoft”.  It has all kinds of DMV Queries which will help you out.

Ha Ha…now you can see “SREE\Sreekanth”(the Bad guy) is the who is the Root cause for all this mess. Take a hammer and walk to his desk(if he’s nearby to you) Bangggggg on his head ;p (Don’t do it!!).
Once I commit/Rollback and Terminate the session, Database went Offline immediately and  you can bring DB Online back. Or you can just kill that Session which is trying to ALTER the Database without taking the database Offline.

SQL Connectivity Error

Early in the morning, one of my developers called me at my BlackBerry saying “Our UAT Server is down, application is not able to connect to backend, We need your immediate assistance”.
Well, a typical start for the day with a typical SQL Server connectivity Issue!
The Error msg what they were getting when they tried connecting using SSMS is as shown below

Well, my first thought is to ping the Server. It’s ping able and interestingly i was able to connect to SQL Server using my account from my client SSMS. So it ruled out most of the probabilities right there! Then i went into SQL Server error logs(My Fav Place to look at for any issues, if not yours, make a practice of looking at error logs very often from now on). It was repeatedly saying 

Error: 18456, Severity: 14, State: 16.
Login failed for User ‘AppppUser’.
What does this mean to us? State 16 says that the Default Database of the login is not available.
Okay, I glanced Object explorer and also i queried sys.databases to look at all the databases status(es). All were “Online” and were in “Multi_User” mode. Hmmm….had a sip of my coffee and i looked at the login properties and opened user mapping tab and it yelled at me throwing below message







Okay…It’s time to think what’s happening. I was curios to know what exactly is happening on this SQL Instance at that moment and i queried few of my favorite DMVs “sys.dm_exec_requests and sessions” and there was a session which was creating blocks for many other user sessions and i captured the affected Database from results of above mentioned DMV’s. Then i tried to expand the database objects for that Database from Object explorer and it was throwing at me all kinds of timeout errors! By this time i was sure that there is something tied up with this database for the login which is causing issue. Immediately i jumped into login properties and verified for the default Database. Voilaaaaaaaaa….there we go! The default database for that login is the affected database. I changed the Def database to Master and it worked like a charm. Now i started investigating who is that “Bad guy” who is holding LOCKS at database level and not releasing them. It’s the Developer who asked me for help (Poor Guy). It’s his Sleeping Session which created all this mess. Once i killed his session, everything looked clear in my SQL Server and now it was time to grab one more coffee(I’ve a valid reason to enjoy my coffee now:))


Epitome: Don’t just always look at remote connectivity settings of your SQL Server when you face such issues. Start your Investigation from SQL Server Error Logs. Trust me It’s the Best of Best friend for any SQL DBA.