The Multifunctioning DBA

May 30, 2011  7:00 AM

SQL Mail issue update Resolved

Colin Smith Colin Smith Profile: Colin Smith

The other day I posted about an issue I was having with SQL Mail after doing a P2V of a server and also installing SP4 for SQL Server 2005. The root issue was that Outlook would not work on the server and I do not know why. But thanks to Twitter I found sp_send_dbmail and pushed that to our dev team and talked them into using it. I think this is a better way to go since now we are off SQL Mail and on to native SQL Server 2005 dbmail. So our Devs have now changed all the jobs to use the SP and now everything is working as it should.

May 27, 2011  11:59 PM

Powershell Start and Stop -job

Colin Smith Colin Smith Profile: Colin Smith

Today I finished writing a couple scripts that are going to be gathering perfmon counters and pushing them into a DB for me. This is great but I am going to need to run two separate scripts that do this at the same time. So I found Start-job and End-job. these are great because now I can just write my scripts to run in an infinite loop and I will control the stop time by running a stop-job when I am ready for it to end.

I did find something that I was not expecting though. In my scripts I use invoke-sqlcmd and I have the snapin loading in my profile so I did not think I would have an issue. However, after I started one of my scripts and let it run for a while, I checked my database and no data had been inserted. this is strange since when I execute the script without start-job the data flows in as expected. I did a receive-job -id 9, 9 was the id of the running job, and it showed me the output of the script and I saw errors about invoke-sqlcmd not being a cmdlet. WHAT… So I modified my scripts with a add_snapin function and I add the snapin in that function that I call at the beggining of the scripts. Now they are working great.

Love that I can run both jobs in the background and check the status of them as I need to and stop them when I am ready to.

May 27, 2011  6:05 PM

Long Weekend

Colin Smith Colin Smith Profile: Colin Smith

Well I hope that everyone enjoys the long weekend this year. Today is Friday so I hope that you are able to take it easy today and all weekend. I have to drive about 7 hours to go to a wedding but it should be fun. I am looking forward to getting out of town for a few days and as far away from a computer as I can. Laptop will be with me in case I need to do any work bu my fingers are crossed that it will be a good weekend.


May 26, 2011  11:00 PM

SQL Server Clustering time on Node

Colin Smith Colin Smith Profile: Colin Smith

I was asked to find out when the last time my sql server instance had failed over on the cluster. I thought mm I should be able to get this from a dmv or something but I could not find out how. Looked at the cluster dmv’s and just did not find what I am looking for. Threw the question out on #sqlhelp and I got two answers and they are both simple and I did not even think of them. I was trying to hard to figure it out and did not think to look at the simple things. @sqlpoolboy had both answers for me.

1. Check the created date of Tempdb – So simple and it makes so much sense. Since tempdb is re-created everytime the instance starts that would tell me when.

2. Since I am running SQL Server 2008 he provided this query for me as well.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Both are pretty simple but I never would have thought of option 2 but I am kicking myself for not getting option 1.

Thanks again

May 26, 2011  9:00 PM

Training plan

Colin Smith Colin Smith Profile: Colin Smith

So like I said before, I am lucky and my boss sees value in sending me to training and I do my best to show off that the training works by improving things when I get back. Training can help save the company money in the long term for sure. One thing that I told my boss is that if it costs 5k to send me to training I think I can save the company that amount of money multiple times by implementing the skills that I acquire. Example is that I know we can do some consolidation and save money on licensing and hardware, I will be able to do this consolidation more effectively if I go to training and really know how to get the most performance out of my servers. If I can consolidate just two sql server enterprise instances then I have more then paid for the cost of my training. So in the big picture it is not that expensive.

Anyway, the plan that my co-workers that are not as lucky as me, they actually work for a different company but we work together, have come up with is that we are going to allocate one day a week to training. Just us. We all have different skills sets and we all have different levels of experience. So why not leverage each other and train each other in the areas that we are strongest. This will make us all better at our jobs and we will be able to push each other to get better. Now of course you have to have a boss that will sign off on something like this but it really costs the company little to nothing. Sure some time, but we will all be in the office and available if needed. It might be hard to train everyone not to bother us unless it is something that is a big deal but I think we can manage that.

So I challenge you to come up with a solution that will help you get some training that is not all on your own time and dime. let me know what ideas you come up with as well. I will also keep you posted on how this works out for us.

May 26, 2011  5:20 PM


Colin Smith Colin Smith Profile: Colin Smith

Today a coworker of mine forwarded a blog post to me and it is very well done and very interesting. If you work in a company that does not have training budget or just says that the cost of training is too high, you might want to send this to your boss. I am lucky and my company is willing to invest in me and send me to training, as is evident by me going to the SQLSkills immersion event this August. But I think I will still send this to my boss as it might be something he can use when he goes to hiss bosses about training.

Take a look at this and let me know what you think about it and how your company deals with training. I work side by side with some guys that do not have the luck that I do and they do not get the opportunity to attend much training. But we have a plan and I will post more about that in another post after you have read this article.

May 25, 2011  10:44 PM

SQL Mail issue Update

Colin Smith Colin Smith Profile: Colin Smith

Thanks to Twitter I have had a lot of help with the SQL Mail issue that I am having. Unfortunatly I still have not been able to resolve the issue at this time and I am still waiting on hold for MS to help me out with the main issue that is Outlook will not correctly set up the mail profile for some reason and continues to crash. I did however get some good advice from @sqlsoldier and @sqlconcepts. I had mentioned just doing away with SQL Mail and using database mail instead.. This way I do not care about outlook and the mail profile. My issue was that the DTS packages are using a send mail function and DTS does not support DB Mail. They had the idea of replacing all those send mail functions with a t-sql script and call sp_send_dbmai. I have not yet been able to talk to the app devs about this but it seems to me like this is the way to go.

This is yet again more proof on how AWESOME the SQL community is and how Twitter can be very helpful.

Thanks everyone for your help and if MS ever gets on the phone I will still let you know what they say and if they resolve my issue.

May 25, 2011  8:29 PM

SQLSkills Twitter Quizes

Colin Smith Colin Smith Profile: Colin Smith

The team at SQLSkills does a quiz on twitter every Wednesday and if you are not already participating in them I highly recommend that you do. I have only ever answered one question correctly and that was only because the answer was in a previous question. Even though I have not really answered any of the questions correctly I still attend the quiz and I learn a lot every week. They are usually 10 questions but they are tough and they always provide the correct answer and they usually provide a link to blogs and or Books Online that provide the detail about the answer. So I would start following #sqlskills if you are not already and take advantage of this fun way that they are handing out free knowledge.

Hope to see you on twitter answering the questions.

And thanks to all of the SQLSkills team for doing this for the community. I know I and many others appreciate it.

May 25, 2011  6:28 PM

Legacy SQL Mail on SQL Server 2005 SP4

Colin Smith Colin Smith Profile: Colin Smith

I have a server that is running SQL Server 2005 SP4 and has a bunch of old legacy DTS jobs. These jobs leverage Legacy SQL Mail to send notifications on job steps. I had to P2V this server in order to install SP4 that was required to fix another bug. After the P2V I was able to install SP4 and that seems to have resolved the other issue we were having. Now, SQL Mail will not work. I have tried everything I can think of to resolve the issue and I can not get Outlook 2003 to run on this server. When I attempt to launch and configure mail it crashes every time. Any one had an issue like this? I am currently waiting on MS to call me back and I hope that they will be able to help me resolve the issue. If you have any thoughts please let me know. @smithco32 on twitter. Thanks and I will follow this post up with the resolution once I get one.

May 21, 2011  9:00 PM

PASS Sumit changing venue

Colin Smith Colin Smith Profile: Colin Smith

A few days ago PASS announced that the 2013 Summit will be in Charlotte NC. This is great news, in my opinion. I have only been to one Summit and it was AWESOME and I recommend it to any SQL Server pro out there no matter what level you are at. I had such a great time but I do not think that I will be able to go this year unless I get chosen to speak, Fingers Crossed!! I will for sure try to attend the conference in 2013. I think it is great to be able to travel and see new places and also this will give users on the East Coast the chance to go without having to spend so much time and money.

Great Job PASS on choosing this local!

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: