T-SQLTuesday #42! The Long and Winding Road

Comments: 2 Comments
Published on: May 14, 2013

 

 

 

TSQL2sDay150x150The Long and Winding Road

It is time for another installment in the monthly blog party for SQL Server professionals known as TSQL Tuesday.

This month we have the pleasure of being hosted by Wendy Pastrick (blog | twitter).  The topic for the month requires a bit of introspection (almost like the self-evaluation piece of an annual review).  Quoting direct from her blog, here is the gist of the topic:

Here’s what I thought it would be fun to share with the community this time around – we all experience change in our work lives. Maybe you have a new job, or a new role at your company. Maybe you’re just getting started and you have a road map to success in mind. Whatever it is, please share it next week, Tuesday May 14th. Make sure you note what technologies you find are key to your interests or successes, and maybe you will inspire someone to look down a road less traveled.

longuphillbw2For me, this is an interesting topic.  It was my theme of choice last month with a major announcement (see here).  And because of that, I am even using the same image – slightly changed.  Only this time, I will go back a bit further into my career and the road I traveled to get to today.

I am going to go back to a decision point in my career that had a huge impact on where I am now.  That decision point was shortly after having moved to Las Vegas about four years ago.  After having moved to Las Vegas, I made the decision to become more active in the SQL Community.  The first step was to regularly attend the user group meetings.

Prior to moving to Las Vegas, I was a member of PASS.  I had been to SUMMIT.  I knew of the local user group meetings in the Salt Lake City area.  I just never forced the issue due to timing etc.  This was something that I felt needed to change.

By making that conscientious decision, I became more involved in the online community. I soon started presenting.  And before long, I was involved in the scheduling of speakers for the Las Vegas UG.

By becoming more active in the community, my skillset started to rapidly grow.  I found myself blogging more and researching more about SQL Server.  I really started to learn about SQL thanks to that decision.  Prior, I feel I was good.  Now, I feel I am much better because I invested more time and effort and I am trying to share the skills that I have learned.

I have said it before and it is worth saying again.  If you really want to learn a technology, try teaching it to somebody.  By taking on that extra step, you will find yourself researching a bit more and you will find that you may have to answer questions about it that you had never considered until you tried to teach it.  Being active in the community has helped me to become better at my trade.  I am sure it will help others as well.

 

May 2013 Las Vegas UG

Categories: News, Professional, SSC, SSSOLV
Comments: No Comments
Published on: May 14, 2013

Spring is in the air, I think.

bbqWith that scent in the air, we have a nice juicy topic coming up this month for any and all that are interested.

Chad Crawford will be presenting to the group on the tastiness that is Service Broker.

Service Broker in Action

SQL Server Service Broker is a messaging framework built into the SQL Server engine. It enables SQL Server to handle messaging between servers and applications with light setup and overhead. The flexibility of the framework enables Service Broker to queue event notifications, task execution requests or other messages while leveraging the strength of SQL Server transaction management, reliability and recoverability. In this session we will see how to set up Service Broker, discuss case studies where it has been implemented in industry, and step through a practical example implementing an audit log.

Session Level: Intermediate

Chad Crawford’s BIO

Chad has been working with database engines for 14 years, the last 12 focused specifically on SQL Server. He has filled a variety of roles spanning architecture, development and administration. Chad is currently the Database Architect at Henry Schein Practice Solutions in American Fork. When he isn’t optimizing a query, you will find him running, dreaming about airplanes, or looking for a new strategy board game.

Meeting Details

Attendee URLhttps://www.livemeeting.com/cc/UserGroups/join?id=KWRMQ3&role=attend

Meeting ID: KWRMQ3

 

Lost that Single-User Connection?

You have changed your database to single_user mode to do a task.  As you go about your business, you lose track of which connection was the single_user connection.  You start closing connections and mistakenly close the session that was your single_user connection.  Now you are unable to start a new single_user session.  It would seem that somebody or something has taken your connection.

Today, I am going to discuss some things you may do to get around this problem.

The first thing that may come to mind when you encounter this is “Oh crap!”  Well, no need to get too terribly worried (not unless you really hosed something up and you are trying to fix it real quick before the boss notices).

The next thing you may think of trying is how to circumvent the single_user mode.  And during that thought process you may be thinking that single_user does not really mean single_user so you might try something like start a DAC session.  Well, let’s go through that and see what would happen in a DAC session if your single_user session is stolen.

I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists.  To start a DAC session, I am going to point you to a previous article I did on the topic – here.

To ensure I am using a DAC session, I am going to issue the following query.  This will ensure I am in the right session and that DAC is in use.

[codesyntax lang="tsql"]

[/codesyntax]

In my case, this results in an endpoint with the name “Dedicated Admin Connection” and a spid of 84.  Good, I am in the correct session for the rest of this test.  Next, I will issue a Use database statement.  I have created a test database called ClinicDB.  So I will issue the following.

[codesyntax lang="tsql"]

[/codesyntax]

I get the following result.

[codesyntax lang="tsql"]

[/codesyntax]

So, that blows that idea right out of the water.  It shouldn’t really have been a consideration in the first place because single_user really means just that – single_user.

Now What?

Well, what do you think we could do now to circumvent this little problem and get that single_user session back?

That requires a little investigative work.  It is time to find out who has taken the single_user session and politely ask them to give it up.  To make that task a little easier, we could modify the previous query to find out who has that single_user session (thus limiting how many people we have to ask).  I have modified the following query to use sys.sysprocesses so I could limit the results to the ClinicDB.  This is a limitation of SQL 2008 R2 and older versions.  Getting the database reliably means using sysprocesses.  Despite the database_id being available in other related DMVs, it’s just not that easy.  One would think you could use sys.dm_exec_requests.  But if a request is not active, an entry won’t exist for that session.  This problem is fixed in SQL 2012 since the sys.dm_exec_connections DMV now has the database_id field.  Enough of that birdwalk and on to the query.

[codesyntax lang="tsql"]

[/codesyntax]

I chose not to do an entirely new query to simply demonstrate that it was possible with a very small tweak to what has been already used.

Now that you know (in my case I can see that I have a session open with ID = 80 that is connected to that single_user database), I can walk over to the person (knowing his/her login id and computer name) and politely ask them to disconnect.

In the end, this is really an easy thing to resolve.  Sure it may take some people skills – but that doesn’t make the task too terribly difficult.  Next time this happens to you, just remember you can run a quick query to find who has sniped that single_user session.

page 1 of 1






Calendar
May 2013
M T W T F S S
« Apr   Jun »
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, October 24, 2014