The Multifunctioning DBA


July 27, 2011  8:00 AM

SQL Nexus

Colin Smith Colin Smith Profile: Colin Smith

I use SQL Nexus all the time to identify bottlenecks of my SQL Servers and I absolutely love the tool. I ran into a small issue though where I could not always load the *.trc files. It would start and fail very quickly and move on to importing the other data. While the tool was still useful it was not as useful as it is when the *.trc files import correctly. With them I can dig in and see the actual queries that were causing the issues. I found that the problem was my own. When I launched SQL Nexus I would create a new database for my traces and I would put a ‘_’ in the database name. It does not cause errors and is a valid database name but as soon as I took out the ‘-’ all my *.trc files will import with now issue.

hope that helps and happy troubleshooting

July 26, 2011  7:00 PM

T-SQL cursor to kill all spids connected by same program

Colin Smith Colin Smith Profile: Colin Smith

I was recently asked by one of the application teams to come up with a way to kill all spids associated with a particular application once a day. Well I am not a t-sql guru by any means so I took this as a challenge and for once I did not pull out powershell but decided to go with T-SQL. With power shell I would have run a statement to give me all the spids in an array and then I would have looped thru them all running a kill. But I know I can do something similar in T-SQL so I fired up SSMS and away I went.

First I had to find out how to get the spids. That did not take long at all.

Select spid from sys.sysprocesses where program_name = ‘name of app’

That gives me all the spids, now to kill them all. A Cursor should do it. May not be the best way, I do not know and if you know another way please leave a comment and let me know.

So here is the final code that I am running.


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

declare @spid varchar(10)

declare kill_spid cursor fast_forward for

select SPID from sys.sysprocesses where program_name = ‘app name’

open kill_spid

fetch next from kill_spid into @spid

while @@FETCH_STATUS = 0

begin

exec (‘Kill ‘ + @Spid)

fetch next from kill_spid into @spid

end

close kill_spid

deallocate kill_spid


July 26, 2011  5:45 PM

SQLSkills Training

Colin Smith Colin Smith Profile: Colin Smith

Just got my reminder from the hotel about my stay starting this Sunday for the SQLSkills immersion event. I am very excited for this and can not wait to get there. I have a lot going on at the office and I think that this training will help me better deal with some of what I have going on.

Also looking forward to meeting other DBA’s and doing some networking. Should be a ton of fun and I am really looking forward to it. Hope to see you there and if you are not going to this one keep an eye out as I will try to do some posts about the training.

thanks


July 21, 2011  10:17 PM

Free SQL Server Training every week

Colin Smith Colin Smith Profile: Colin Smith

BrentOzarPLF is has started a new thing that they are calling Technology Triage Tuesdays. This is great and the first one is online at BrenOzar.com. These guys are great and I thank them for doing all this work and giving it away to the community. Go check it out and take advantage of this. BrentOzar PLF is made up of four SQL Server Professionals and two of them are MCM’s.


July 15, 2011  4:59 PM

Spotify Invite

Colin Smith Colin Smith Profile: Colin Smith

Spotify is a music streaming service that has been around in the UK for a while now but it just launched in the US. right now you have to be invited to Spotify to get a free membership. Currently Spotify has an invite only free streaming service and you can get your invite at this link:

http://www.spotify.com/us/bruno-mars/

I am leery of putting my email into anything on the web but I did not this because I really would love to check it out and see if it is better then the rest of the competion out there. I got my invite in less than 1 hour so I hope it works for you as well. I will post more once I have figured it out and tested it.

have fun


July 12, 2011  10:28 PM

Netflix Price Changes

Colin Smith Colin Smith Profile: Colin Smith

Just saw that Netflix is increasing the prices. For me this means that I will drop my disks all together and just do the streaming for $7.99 a month. I dropped cable in order to save money not just to ship it all over to Netflix. So now I will just use Redbox and or Blockbuster or Vudu when I need to see a newer movie that is not on Streaming via netflix. To me it is not worth it to have 2 blue rays checked out at a time plus streaming fro $18.00 more per month. Sorry Netflix, I love you but I just need the streams….


July 12, 2011  9:02 PM

SSPI Context Errors

Colin Smith Colin Smith Profile: Colin Smith

I recently changed a SQL server from running as Local System to running as a domain account. When I did this I started to get the dreaded “Unable to Generate SSPI Context” error. I tried all that I could think of and had discussion with some people on Twitter as to what the issue may be. Eventually I had to open a case with MS to get it resolved. I had to use ADSI Edit to add the SPN’s to the service account manually. Once I did that Kerb auth started working but I am still not able to fall back to NTLM if needed. I will hopefully have more information on all that soon as I am going ot continue to work with MS and find the root of all this evil.


July 8, 2011  10:04 PM

Google + first impressions

Colin Smith Colin Smith Profile: Colin Smith

I have never been a big fan of Social Media and I really have not been a big fan of how some big social media sites do not seem to care about privacy or at the very least it is not at the top of the list for them. I just got a Google+ Invite thanks to Jorge Segarra (Blog | Twitter).  I have not been playing around with it long but I tell you what I really do like it thus far. I love the Circles and that I can share with just certain circles. I love Spark, it is nice to get relevant search results about what I like ready to go when I am ready for them. I have not had time to try the hangout feature yet but man I love the idea of it and I see many ways that I can use this other than just chatting with friends. I love how easy it all is so far… Drag and drop into Circles… That is fantastic..

I will write a much better and deeper review later after I have played with it but I had to get this out here. If you need an invite hit me up by leaving a comment and I need your gmail or hit me up on twitter @smithco32


June 30, 2011  6:46 PM

Great “Golden Rule” Post

Colin Smith Colin Smith Profile: Colin Smith

Check out this post by Paul Randal(Blog | Twitter)

http://www.sqlskills.com/BLOGS/PAUL/post/The-Golden-Rule-maybe-just-optional-now.aspx

I love it and think he is right on the Money. Why is it so hard for people to be polite and respectful of others. And especially to teach your kids to be polite and respectful.


June 30, 2011  8:00 AM

My New Favorite Web Site

Colin Smith Colin Smith Profile: Colin Smith

This has probably been around for a long time and I just did not know but I do now. Ever have someone come up to you and ask a question and you think to yourself,, “Self, They did not even try to figure this out.” Well that happens to me all the time and now I have a good way to respond to them. Have them send it to me in an email, do a search for them using terms they put in the email, like error numbers, and then see what pops. If it is a easy thing to search on then go to:

Let Me Google That For You

Pop in your search and it will give you a link to send back to them and it will get the point across. Great site and I hope that it will help you teach people how to search and think for themselves.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: