The Multifunctioning DBA

January 18, 2010  6:34 PM

PASS Summit

Colin Smith Colin Smith Profile: Colin Smith

I am trying to get my company to pay for me to attend the PASS Summit 2010 in Seattle Washington. I am still pretty new in the world of Databases but I think that attending the Summit would be very good for me. First I might get to meet some of the people that I read daily and respect so much. That would be really cool, but I think more importantly than that is getting to know people like me that have their hands in the trenches everyday. I would love to hear the horror stories about Database Corruption and how they recovered, or perhaps a Disaster and recovery. I prepare for DR all the time but have never, knocking on wood, had to go through one. Listening to real problems and how to deal with them is what I am most interested in. Especially performance, I have not really been able to get to deep into performance tuning but man I want to. I think that the presentations at the Summit will be invaluable to me as well.

I just sent my boss a breakdown of the cost to send me and it was just over $2500.00. That is a lot but not really when you think about it. I am pretty much the only SQL Guy at the company and I am still new but I have identified over 140 instances of SQL Server running in the company. I am still attempting to make sense of all of them and get my basic health check script working on them all. But I have to say, with that many instances and all that data, I think it would be a good investment to send me to get more knowledge and meet people that have more knowledge than I do. I have my fingers crossed and I hope that I will see you there.

January 8, 2010  7:19 PM

Find SQL Server Instances using Powershell

Colin Smith Colin Smith Profile: Colin Smith

As I had mentioned before, I recieved a network scan that lists the host names of servers that have some sort of SQL Server running on them. Now I need to get a list of all instances that are on those servers. I thought I could do it in Powershell and I was correct. I know that all instnances of SQL Server have to have a SQLSERVER service associated with it. Knowing this I thought I could use the get-service cmdlet in order to list all the services on each host. This was a great idea but can not work. I am using Powershell V1 and the get-service cmdlet will only work on the local host. I was not going to install Powershell on each host in order to find the instance names, that would be a ton more work and I am into doing less work not more. I think most IT people in general are that way.

After thinking about another way I could get what I needed, a light turned on and shined down on my from the Powershell gods, Jeffery Snover perhaps, and I knew what to do. WMI is the answer, why did I not think of that sooner? Anyway, I can do a get-wmiobject -computername “name” win32_service and that will return a list of services on that machine. Now all I have to do is filter the results to get only that which I desire.

Just want to mention that the script does have one flaw that I did not fix. If you do not have access to connect to WMI on the server the script will stop. If the server is unreachable for some other reason, RPC Unavailable, that server will be put into a list of servers that were not available and the script will continue.

I think that this script could be useful for others in a similiar postion to me. Walking into an environment where the previous SQL Admin did not really leave any documentation for you so you are walking in blind with no guide at all. Please let me know if you have any questions.

############################################################################## ## Author: Colin Smith ## ## Script: Get_Intstance_names.ps1 ## ## Purpose: This script will read in a file of hotnames that has been ## ## Provided of servers with SQL Server running on them. This ## ## script will then look at the services on that host to find ## ## the instance name if the instance is named. If the instance ## ## is a default instance the script will also report that. ## ############################################################################## $servers = get-content "C:\servers.txt" echo "Server, Instance" >> "C:\sqltab.txt" foreach ($server in $servers) { $instances = Get-WmiObject -ComputerName $server win32_service | where {$ -like "MSSQL*"} if (!$?) { echo "Failure to connect on $server" >> "C:\failures.txt" echo "Failure to connect on $server" } Else { $instancenames = @() foreach ($name in $instances) { if (($ -eq "MSSQLSERVER") -or ($ -like "MSSQL$*")) { $instancenames += $ } } foreach ($iname in $instancenames) { echo "$server, $iname" >> "C:\sqltab.txt" echo "$server, $iname" } } }

January 6, 2010  10:33 PM

Lots of Work to be done

Colin Smith Colin Smith Profile: Colin Smith

A few months ago I was transferred to my companies parent company. I think that this is good news for me so I am happy about it now. I have been asking, for about a month, to do a network scan in order to identify servers that have SQL installed. Due to the holidays and scheduling I did not get this right away. I just got a list today and so I wanted to get going and see what I have to deal with. First of all I have 142 servers that have been identified on the scanned networks, more to come, that I have to start looking into. I have a script that will go out and get some basic information about each instance as long as I have an instance name. I do not, I only have the host name of the servers. That is OK though as I think most are default instances. Anyway, I started my script and away it went. It scanned about 5 servers before running into one that is not a default instance. Because of this I will write another script tomorrow to go get all the instance names. I will try to post that script as well. Before it stopped though, I found one server with 117 Databases on it. WOW. It is also running SQL Server 2000. I am not really happy about that. Also, I see one Database is about 6 gigs and has a log file that is actually larger then the database file.

All that being said I think that I am going to have a lot of work to do in the next couple of months. Should be fun though and I am sure that I will run into some things that I have never seen before. You know, like a SQL Server 2000 instance with over 100 databases on it.

I will try to get a script together to find all the instances and when I do I will post it here. I will also post on some of the things that I find over the next few weeks and I will post how I handled some of the issues that I run into. Like I said, should be a blast and perhaps this is really what I needed to get the passion back.

January 5, 2010  5:11 PM

2010 Resolutions

Colin Smith Colin Smith Profile: Colin Smith

2010 Resolutions

I am usually not one for resolutions, but this year is a bit different for me. In the past year I have had some things in my life change. Some for the better, and some changes were not for the better. I try to keep a positive outlook on life and see the positive even in the negative. Sometimes this is very hard. A couple big changes for me this year were.
1.    My job change.  In 2009 I was transferred from my company to the parent company. Now I support many sites but my scope of supported Databases has decreased. I now am a SQL Server DBA where before I was also doing Sybase and Oracle. I still am on call for the previous job so I still do Sybase and Oracle, although it is much less often now. (It is a strange situation that I am in)
2.    This change is more personal and does not have as much to do with work. I think that it will affect my work, and I am hoping in a positive way. I was diagnosed with type 2 Diabetes. Not cool but I put myself here and now I have to work through it. I started strong after finding out and made some big changes but I have fallen off the wagon so to speak. This is something that I have to change or I could die. I hope that in making the positive changes in my life to help with the Diabetes, that will translate into more energy and a revitalization of my passion for work. I still have the passion, just not as much as I used to and I really want to get it back.
With these two large changes in my life last year I think that I need to make a few changes in my life this year to work everything out.
•    First is my health. I have starting working out a plan to make the changes that I had made after           my diagnoses along with some new changes that will help me stay on track. My goal is to lose 80 lbs and keep it off for the year. Then next year all I have to do is keep it off. That sounds way better than losing it. If I can do that then I think that will do a couple things for me.
o    This will get my A1C in check and I can get off the meds that I am on. I HATE taking pills.
o    I think this will give me the added energy I need during the day to be more productive then      I am. Right now I get the job done but I really feel like I can do more.
•    Now to work. I want to continue to learn but I want to do it in bigger leaps. I want to read more, I need to complete my SQL Server Certification and also start on the 2008 Certification. I need to figure out the mess that the job change has gotten me into and I need to get to work on that. Right now I have no clue about all the servers that I am now in charge of. I like to be proactive and not reactive and I right now reacting is all I can do. I am in the process of gathering a list of instances but even after I do that I have a lot of work to do in order to “Get to know the servers”. This is very important. I need to do a health check of the servers, and then start getting some baselines so that I can do performance monitoring on them all. This is going to be a huge undertaking but it is a challenge that I welcome and look forward to. I know I have a lot to learn so I will love it.
•    Another thing that I have done is set up a twitter account. I am so anti social networking but I really would like to become more of a member of the community. My twitter name is smithco32 but I have yet to send a tweet. I have no followers at this time and I will only tweet when I have something real to say about SQL at this time anyway. I am following two individuals and a group. I follow MR. Denny and Brent Ozar. Both are SQL Server MVP’s and both have great blogs that I read daily. I have learned a lot from them and strive to be more like them in the SQL world. Also Brent Ozar has a post about following groups on Twitter and I am following the sql-server group that he had put together.
Well I guess that is about it for changes that I am going to make in 2010. I do not think the goals are to lofty but I think that they are good for me. If you have any thoughts on how I can become a better part of the SQL Server community please let me know. Also let me know what you are going to be changing in your lives this year.

January 1, 2010  7:00 AM

I am Back

Colin Smith Colin Smith Profile: Colin Smith

Sorry for the couple months that I have not posted. I have been very busy but not really with anything fun. I have had some family things going on plus all the holidays so again I am sorry. I am back now and so happy to be in a new year. I hope that everyone had a happy and safe and fun Christmas and New Years. I know I had a good time!
One of the worst things that happend to me while I was away from posting is that I lost a dog. We had to put Sherman down and he was a great dog. It was really hard on me and my wife. Here is a picture of Sherman so please take a moment for him.

Sherman in small bed

Sherman in small bed

He was a lot of fun and just a pretty dog. He is and will be missed. Sherman passed on 11-11-09.

After a while my wife wanted to go to the pet shelter where we found Sherman to find another dog.  We went and we got a new dog. By no means does the new dog replace Sherman and he is a totally different type of dog. Sherman was very mellow and calm. The new dog, Cash is his name, is very hyper and playful. I love it, taking him to the park to play ball is a blast. Anyway, here is a picture of Cash.

Cash in Big Bed

Cash in Big Bed

Well Merry Late Christmas and a Happy New Year to all. I will be posting again all month and looking forward to getting back into it.

October 31, 2009  3:00 PM

Happy Haloween

Colin Smith Colin Smith Profile: Colin Smith

Happy Halloween from Hawaii everyone. I saw this and thought that I would share with you as well. I would love to go to one of the houses in this video. I love Tech and these are using plenty of it.

Cool Stuff

Have fun and be safe.

October 29, 2009  8:00 PM

Windows 7

Colin Smith Colin Smith Profile: Colin Smith

I have played with one of the preview releases and I liked it just fine. I just read some good info on Engadget that leads me to believe that I will like Windows 7 just because of the media center upgrades that have been done.

I am excited to hear about media center starting to play nice with DLNA devices and not just Media Extenders. This means that I will be able to use my PS3 to stream music via windows media center and no longer have to use a third party app. Not that I mind using a third party app to do so but I think that everyone needs to get along and talk to each other when it comes to media sharing. I think that most media will be online soon and I think that the more devices that can talk nicely together the better off we will be.

Based on just that I can not wait to get my hands on Windows 7 and get the install complete.

October 28, 2009  8:00 AM


Colin Smith Colin Smith Profile: Colin Smith

Well I am very close to heading out on a nice 10 day vacation to Hawaii. I have been looking forward to this for about 6 months and it is finally here. I fly out on Wednesday morning and I will not return until the following Friday. I may post while I am on vacation of I see anything or do anything extra cool. Right now I know that we will be doing some snooba diving, going to the blacksand beaches, and checking out the lava on the big island. I will have pictures and post some of the better ones up here for all to see.

October 27, 2009  3:30 AM

Active, Active, Active

Colin Smith Colin Smith Profile: Colin Smith

Last week I got the chance to work on an Active, Active, Active SQL Server 2008 Cluster install. This was new to me and very fun. We have three physical hosts all using SQL Server 2008 Enterprise Edition. The Servers are also running Windows Server 2008 Enterprise Edition. I worked with one of our windows admins to do the install. First the windows admin set up the windows cluster and assigned all of the storage to the cluster but did not assign it to an application or service. Once that was done we had to set up the Distributed Transaction Coordinator and give it its storage. Now we started the install of SQL Server 2008. Since all of the storage was owned by one server we installed all three instances, one for each node, of SQL Server on that server and that created the different cluster applications for us. Then on each of the other servers we added a node three times, again once for each instance. Now that is complete we can fail over the resources for any instance to any of the physical nodes. Very cool.

October 26, 2009  4:26 PM

Databases not accessible

Colin Smith Colin Smith Profile: Colin Smith

Just got a call that three databases on an instance where many reside were not accessible. Users were getting errors about not enough disk, memory, or the files were inaccessible. I logged into the server and found that the databases were not offline but could not be accessed. I looked into the errorlog and found the following error:

‘The transaction log for database ‘database_name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases’

Well I looked around and I did not find anything wrong. Disk were logs are kept had just under 100 Gigs free and the ldf files were not in a read-only state. Based on that I offlined the databases and then attempted to bring them back online. SQL found the MDF and the LDF files, went through recovery, found no issues and brought the databases back online.

Still not sure exactly what happened. It is strange also since all the databases use the simple recovery model. Based on that I am not sure how the log was full but there you go.

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: