The Multifunctioning DBA

November 27, 2010  10:00 AM

SQL Saturday Phoenix Sessions

Colin Smith Colin Smith Profile: Colin Smith

I just checked out the SQL Saturday Phoenix Homepage and I was very impressed by the number of submitted sessions that are on the board and we have a couple of sessions that have been suggested as well. If you head over to the schedule page you will be able to see the sessions that have been submitted and the ones that have been suggested. Notice that nothing has been accepted or turned down at this time. That is because the call for speakers is still open. I am impressed with the sessions that have been submitted thus far and I am hoping to see some more great ones as well. I think that both of the suggested beginner topics are great as well. I would love to see someone in our communitity step up and do sessions on Backups and one on some basic Troubleshooting of Failed jobs. Both have been suggested at a beginner level and I think it would be awsome for someone to step up do a session on these topics. Both are good topics and I am sure will be very helpful to many of the beginners in our community. Lets get more sessions submitted and try to make this event great, if we can do good for our first one then just imagine how great we can make the next one.

Please come out and learn and or teach. I hope to be doing both. You do not need to be an expert at everything or really anything. If you have good knowledge in an area come out and teach the rest of us. We all want to learn more and I bet you have something to teach us all.

November 26, 2010  8:00 PM

SQL Saturday 47 Phoenix

Colin Smith Colin Smith Profile: Colin Smith

So, I hope that you have all heard that SQL Saturday Phoenix is back on!! The event was postponed originally because the weather here in Phoenix is so HOT in July. So the Event is now set for Saturday February 19th at

Chandler-Gilbert Community College Pecos Campus
2626 E Pecos RoadChandler, AZ 85225

I really hope that you can all come out and have a great day of learning with us. I still hope to be presenting on Powershell for the DBA. This will be an intro into Powershell in general with a bit of a focus on how it can help us out as DBA’s. I hope to get some sponsor swag for my session and I still have the Powershell Stickers that were provided to me by nsoftware and I thank them again fro providing these to me and I look forward to handing them out. I am hopeful to get the same response from Idera as I did before. I am not saying this is what I will have but I certainly hope to have a few fully licensed copies of PowerShell Plus to hand out during the session as well. That is the IDE that I use and I would love to be able to hand some out and get some people started with Powershell.

Well please go to the SQL Saturday Phoenix Homepage and sign up. Hope to see you.

November 26, 2010  2:50 PM

Day After Thanksgiving

Colin Smith Colin Smith Profile: Colin Smith

I hope that you all had a great holiday and that you did not do any work. Any way, by the time this post is published I will be sitting at my desk working on Black Friday. My wife hates that I work on the days after holidays most the time and I agree that it is not always the most fun to get up in the morning and make it into the office. However, I really like working the day before and the day after holidays when I can. I have two major reasons for this and they are below.

1. Get a lot done. – I do not know about all of you but I am assuming that things work similar to the way that they do around my office. No one wants to make major changes, or do anything that might cause an issue to any production system during a holiday weekend. That means it is usually quite as far as migrations, system builds, and putting out fires (Knocking on wood now). Also not many people are in the office so not only is it quite as far as work for others but it is actually quite in the office. Not many distractions so it is a great day to get a lot done. At least for me.

2. Good will. – By this I mean that you probably have co-workers that really enjoy taking some time off around the holidays. Don’t get me wrong, I love spending time with my wife and family just as much as anyone but I fine that a little good will in this regard goes a long way. My team knows that I usually sacrifice during the holidays and I think that goes a long way with them when I need some coverage for on-call or I just need some time off work. I am not saying to work every holiday but making that small sacrifice sometimes can go a long way.

Well again I hope that you are not reading this as soon as it posts but I hope that you enjoyed it. Have a great Holiday weekend.

November 25, 2010  10:00 AM

Happy Turkey Day

Colin Smith Colin Smith Profile: Colin Smith

Just wanted to say Happy Thanksgiving to all and I hope that you are all safe and have a great holiday. Hopefully you are not working on DBA issues today. ūüôā

November 24, 2010  6:52 PM

Find Database Files with Powershell

Colin Smith Colin Smith Profile: Colin Smith

I recently had a request to go out and find the logical name and the physical name of mdf and ldf files in sql server. I thought this might be a good place to use powershell and the invoke-sqlcmd cmd-let that is with sqlserver2008 snapin.

Here is an example of what I did to find the files for one specific database:

$files = Invoke-Sqlcmd -ServerInstance “sqlserver” -Database “master” -Query “select name, physical_name from sys.master_files where name like ‘%dbname%'”

$primary = $files[0].name
$primary_physical = $files[0].physical_name
$log = $files[1].name
$log_physical = $files[1].physical_name

Pretty simple but this will give you the name and physical location of the files that you need. Hope that will help. I actually plan on using this is my Monitoring scripts.

November 23, 2010  7:05 PM

Powershell Find Files modified after a date

Colin Smith Colin Smith Profile: Colin Smith

I recently needed to find all files in a specific directory that had been written to after a specific date. I also wanted to include all subfolders in the search. I decided to use Powershell to do this and man was it simple. First you have to define the date that you are looking for and you have to define the path that you are looking into. Like such:

$date = [datetime] “10-20-2010”

$path = “c:\test”

Then once you have that you can just do a get-childitem and look at the lastwritetime property of the file.

Get-ChildItem -recurse -path $path | select name, lastwritetime | where {$_.lastwritetime -gt $datemodified}

and that is it.

Hope that will help you look for files that have been recently written to or even find files that have not been written to in a very long time.

November 22, 2010  3:13 PM

People at PASS

Colin Smith Colin Smith Profile: Colin Smith

I thought I would focus this post on the People that were at the PASS Summit. Really not much to say except the people are a huge part about why my PASS Summit experience was so great. I mean you obviously have access to many smart people like MS employees and some great MVP’s. That alone is great and makes the Summit worth it. Access to these people is not something that you can get everyday. I mean you can access some MVP’s on Twitter and that is nice but not near as good as the face time you can get and PASS. But the surprising part to me was the “regular” people that attend. I put “regular” in quotes because no one I met was a regular person. Everyone was passionate about technology, especially SQL Server, and all were very smart.

Not only are all the people passionate and smart, but for a group that is usually very introverted, it was easy to meet people and to talk to them. We all have something in common and that really makes it so easy to sit down and get involved in a good conversation and make a new friend. And you will probably either learn something or teach something while talking and most the time both will happen. That makes every second at the Summit so worth the money. You learn all the time not just during the sessions.

Another thing that you must do if you go to a Summit is SQL Karaoke. I usually hate Karaoke and I did not really want to go, but I am very glad that I did. I did not sing but a lot of people did. But it was a fun night of watching people sing and networking. I met some really cool people that night. So even if you do not usually go to Karaoke,  you need to go to SQL Karaoke. You will have fun, and meet some great people. Do not be shy, and I know that is hard for some of us but it is so worth it.

November 17, 2010  4:14 PM

My Pass Summit Experience

Colin Smith Colin Smith Profile: Colin Smith

The Pass Summit 2010 was my first Pass Summit and to put it in to just a few words, IT WAS AWESOME!!!!

I have never been in an environment like that before and I am looking forward to the next Summit already. If you have never been to the Summit it really is just amazing. So many smart people in one place that all have something in Common, A passion for SQL Server. I actually would say that we all had one more thing in common as well and that is the desire to learn.¬† I mean first off you know you are going to learn a ton from the sessions. So many sessions that you can not even come close to going to all the ones that you would like to go to and I am not even including the Pre and Post Con sessions that are not included with the conference registration.¬† But on top of that they do Vendor Sponsored Breakfasts, I did the VMWare breakfast and first off the food was not too bad and the speakers were top notch MVP’s that have and do use the product.¬† The two speakers I am referring to here are Bren Ozar (Blog | Twitter) and Denny Cherry (Blog | Twitter). These guys are just amazing with the amount of knowledge that they have about not only SQL Server, but VMWare and Storage. I learned a lot from listening to them talk for just about an hour. I mean now I want to talk to my SAN guys about doing Database Snapshot backups on the SAN.

Now do not get me wrong, it is not all about learning. I had a ton of fun as well. Monday was the welcome reception and the Quiz Bowl. Some very smart people get up and play a Jeopardy style game to win prizes for a few selected from the crowd. Not only was it informative but hilarious as well. Geeks are some FUNNY people, Probably does not hurt that we are all geeks in the crowd as well. Then we did a Casino Style party sponsored by SQL Server Central. That was a blast and I did pretty well. I think I ended the night in 5th or 6th place in the chip count, at least that is where I was last I checked. Top 4 got some great prizes.

Any way the entire event was just about as great as one could hope for. I also met a bunch of awesome sql server professionals that I hope to keep in touch with and get to know better. Networking at the Summit was one of the many best things about the Summit. I will post more about the Summit later but again to put it in just a few words… WOW it was AMAZING.

November 4, 2010  3:17 PM

Strange Error with user account connections

Colin Smith Colin Smith Profile: Colin Smith

So this morning I was asked to help out with an issue that a user was seeing. The user has an application that uses a local account on the server to run the application and that application uses that account using windows auth to connect to a sql server on another host. So when I got the call I logged into SQL Server and I looked at the SQL Server error log and I found the following.

2010-11-03 15:13:55.27 Logon       Error: 17806, Severity: 20, State: 2.
2010-11-03 15:13:55.27 Logon       SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxx.xx.xx.xx]
2010-11-03 15:13:55.27 Logon       Error: 18452, Severity: 14, State: 1.
2010-11-03 15:13:55.27 Logon¬†¬†¬†¬†¬†¬† Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: xxx.xx.xx.xx]

I found that the application attempts to connect via ODBC connection and I attempted to configure an ODBC connection and I found that I recieved the same error. I searched online for this error and all I found was that this would happen if you were using windows authentication only on the SQL Server and attempting to connect with a SQL Server account. That is not the case for me since I am using windows authentication to connect to the SQL Server. I checked to make sure that the account had been created on both hosts and it was, and it was also a login and user in SQL and the database that it needed.

So I attempted to log into the SQL Server host with this local account and I found that Windows wanted that account to change its password. The password had expired. This was the problem. I went in and modified the user so that the password was not expired and viola…. All is well again.

Hope that will help someone out since I did not find any real help for my particular issue on-line and the error is not so great.

October 31, 2010  12:00 PM

Happy Holloween

Colin Smith Colin Smith Profile: Colin Smith

Here is where I hope to be. Have Fun Be Safe and eat a ton of Candy.

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: