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.
MSSQLTREK 