The Multifunctioning DBA

June 29, 2012  3:52 PM

Wieght Loss Contest Complete

Colin Smith Colin Smith Profile: Colin Smith

Just wanted to give an update on the weight loss challenge that I was participating in. It was a long 8 weeks and it was not easy at all but it was worth it. Not all the final numbers are in for my team and or all the other teams but my numbers are in so here they are.

Starting Weight: 247.1

End Weight: 221.8

Loss: 25.3Lbs

% Loss: 10.2%

and the best thing about it is on the BMI scale I am now not Morbidly Obese, just Obese. So I still have a lot of work to do but it is a great start. I will continue to work towards my goal but I am very pleased with the current results so far. So I hope that some others out there will join in my quest to become thinner and get in better shape. It is really hard to do when our jobs require us to sit in front of a computer all the time. Good luck and let me know if you need anything.

Created by MyFitnessPal – Nutrition Facts For Foods

June 25, 2012  5:34 PM

Great contest running now.

Colin Smith Colin Smith Profile: Colin Smith

If you are not already a member of the SQL Skills Insiders, you need to be. Go sign up now. You are missing out on some good stuff and you should not be missing it. It is free and you get a cool sticker. I am a member and I look forward to the email every month. So please take advantage of the kindness of the group at and sign up.

Also in the last newsletter, that I just received, talks about a contest that is running and is sponsered by They have some great tools and you should check them out. But they have a great free tool called Plan Explorer that I use for sure. If nothing else, check that out since it is free.

But on to the contest….

Click here for the details and please notice the value of this. I have been to the IE1 class and if I had not or if it was for IE2 I would be participating for sure. Paul and Kim to a FANTASTIC job teaching and I learned a ton in that short week. They will make your brain hurt and they will make you a better DBA for sure. So go win this contest and have a great week of training.

Created by MyFitnessPal – Nutrition Facts For Foods

June 20, 2012  11:00 PM

Epic app fail for Database Recovery

Colin Smith Colin Smith Profile: Colin Smith

The other day my team decided that we needed to rethink our recovery strategy for an application. In order to meet the new recovery requirements, we needed to put the database in Full Recovery and take T-Log backups every hour. This would allow us to recover up to the minute with a worst case of 1 hour of possible data loss.  We made the change and set up the t-log backups and verified that they were running and that our log space seemed to be sufficient. We got back in the office in the morning and we noticed multiple alerts of the log backups failing. WTF??? We did not understand. After some research and digging thru logs, we found that somehow the database had changed from full to simple and then back to full again. This confused us, we did not make that change and not many other accounts have the ability to do so. Again we dug in and started looking at when it happened and what was running in the database when it changed. We found a application stored proc that made the change. So because some app dev was worried that a load they were doing would fill up the Transaction Log, he decided it would be OK to change the model, breaking our recover-ability chain, and then do the load and change it back. Like no one would notice. We actually do our job and are aware of what is going on in our databases and we do not appreciate some lazy dev making a poor decision and then forcing us to give the application account elevated access in the database. We have since let the vendor know that they need to change the proc, and we need to work closely with them and re-evaluate the level of access that the application has into the database.

Thought I would share this so that you can keep an eye out for yet even more evil code.

Created by MyFitnessPal – Nutrition Facts For Foods

June 20, 2012  4:18 PM

Catching up and Powershell

Colin Smith Colin Smith Profile: Colin Smith

This morning I was catching up on some blog reading and I saw this great paragraph from Brent Ozar (blog | twitter) about powershell.

“There’s a huge demand for good PowerShell basic training. The majority of sysadmins and DBAs that I spoke with still aren’t using PowerShell. They’re vaguely interested in it, but they’re not getting the education they need. Don Jones and Jeffry Snover ran an incredibly well-attended PowerShell crash course session with almost 1,500 attendees and got very, very positive feedback. Thanks to Microsoft, you can watch the PowerShell session recording now. (You may have to turn the audio *way* up in the video, and on your computer.)”

I think this is fantastic that so many people are finally getting interested in Powershell. I am currently working on a basic PowerShell for the DBA class at and I will let you know when it is out and I will be providing some free classes as well as a discount when it comes out. I have been preaching this for years now and I have been doing PowerShell sessions at SQL Saturday events and they are always popular. I hope that you can all learn something from my course once I put the finishing touches on it. If you are interested in taking a class like this please let my know so I can enter your name into a drawing for a free class. I will be giving away 10 and I will be doing the selection with a powershell script. I will show off the powershell script that I use in the course so you can see it and use it for yourself.

Leave a comment here with your name and email so I can enter you in the drawing.

Created by MyFitnessPal – Nutrition Facts For Foods

June 20, 2012  3:33 PM

Get in Shape

Colin Smith Colin Smith Profile: Colin Smith

For the past few, well 5 weeks, I have been participating in a fitness challenge at work. It is really great. We have a gym on site and they are putting on this challenge. They broke all the contestants out on to teams and my team is made up of pretty much all the guys in my cube row. I am not proud to say it, but between the 8 of us we literally weighed over 1 ton! 2005.6 LBS to be exact. So over the past 5 weeks most of us have really been working very hard to drop the lbs. I personally hit the gym hard 5 times a week and I am really trying to watch what I am eating. I have found some tech tools that help and I will mention those all in a few minutes. In 5 weeks, as a team, we have lost 125.5 LBS and we have a few weeks to go. I will let you know the final results at the end of the month when the competition ends. I personally have dropped 19.7 lbs to date and the lead person on our team has lost 25.9lbs.  8 Teams are in the competition and currently our team is in the lead but only by about 0.5% loss. So we have some pretty stiff competition. Last week all 8 teams combined lost about 30 lbs and my team lost over 11 lbs of that. We do not really get anything if we win but we are all competitive and all pretty out of shape. It is great to have this competition in a team of us that all work so closely together as we can push and motivate each other every day. I know I would not be doing as good without the support from the team.

Now let me share with you some of the tech stuff that is helping me.

1. Fitbit – this little device is great for tracking steps and stairs. A bit pricey but worth it.

2. MyFitnessPal – Totally free and it will synch up with the fitbit. Great app and has some social aspcets as well so add your friends and make sure they are sticking with it.

3. – free and it will synch with fitbit and foursquare. Get free stuff for working out.

Hope that this will help motivate some of you to get out from behind the computer and hit the gym with me. If you want then hit me up on twitter or drop me a comment and we can be friends on these sites and help each other as well.

Created by MyFitnessPal – Nutrition Facts For Foods

June 8, 2012  10:00 AM

Table Partitioning in MS SQL Server

Colin Smith Colin Smith Profile: Colin Smith

How to create a partitioned table in SQL Server. This example is based on weekly partitions by date but it could be anything you need.

1.    Create the filegroup(s) that the partition table will reside on.
a.    ALTER DATABASE databasename ADD filegroup [filegroupname]

2.    Create the file(s) that the partitioned table will reside on on the filegroup(s)
a.    ALTER DATABASE databasename ADD FILE (name = N’filename’, filename = N’Path to file.ndf’, SIZE = initiailsize, maxsize = maxsize, FILEGROWTH = autogrow size) TO filegroup filegroupname.

3.    Create the Partition Function.
(‘20120521’, –week 1
‘20120528’, –week 2
‘20120604’, –week 3
‘20120611’, –week 4
‘20120618’, –week 5 etc…
‘20130610’ –week 52

4.    Create the Partition Scheme
a.    CREATE PARTITION SCHEME [weekly52_scheme]
[parttable1], –etc

5.    Create a heap table on the Primary Filegroup to load the initial data set into.
a.    CREATE TABLE [dbo].[tablename](
[DATE] [smalldatetime] NOT NULL,
[SomeValue] [varchar](16) NOT NULL,
[SomeValue1] [varchar](16) NOT NULL,
[SomeValue2] [decimal](8, 2) NOT NULL,
[SomeValue3] [decimal](8, 2) NOT NULL,
[SomeValue4] [int] NOT NULL,
[SomeValue5] [decimal](8, 2) NOT NULL,
[SomeValue6] [int] NOT NULL)

6.    Load Data (This can be by any process. Verify that the date ranges are correct for the partition function. If not – then drop and recreate the partition function with the correct dates.)
7.    Create the clustered index on the heap table and put the clustered index on the new partition scheme that you created for the partitioned table.
a.    ALTER TABLE TableName
ADD CONSTRAINT Index_Constraint_Name
On filegroupname

8.    Verify that the data is in the correct partitions.
a.    SELECT $partition.PartitionFunctionName( AS [PARTITION#],
FROM parttest AS o
GROUP BY $partition.PartitionFunctionName(

9.    If the data looks correct in the partitions then the table is set up and the data has been distributed to the correct partitions. You can double check in SSMS by right clicking on the TableName and going to the storage tab. You should see that table partitioned = true and you should see the number of partitions that the table spans.

June 7, 2012  6:09 PM

Pass Summit 2012 Session Submission

Colin Smith Colin Smith Profile: Colin Smith

Yesterday I was notified that again this is not the year for me. My session was not accepted for the summit once again. That is OK though, I know that they only have so many spots that they can fill and I know that I was up against some good competition. I will try again next year and during the year leading up to the 2013 submissions I am going to work hard to try and get my name more well known in the speaking community. I think that I am pretty good at it and I think that I have some material that is helpful and I really want to share it.

I have one idea in the works and I will be sharing more about that soon. I hope to have this project complete in the next week or two and if it goes well then I will do more. I am also thinking that I will try to present at my local SQL Server group and perhaps even at the Powershell Users group. I would also like to try and present for the PASS VC and see how that goes. Perhaps try and get into some other smaller conferences and just get more experience. I will make it someday, I will not give up and to anyone else who was denied, I hope you look at it like I do. Just keep working and trying to get better at what you do and get out there more and you will make it as well.

To all that have been accepted to speak at PASS, Congrats and I hope that all goes well for you and that you have a great event. The SQL Server community is made up of some great and smart people and I am happy to be a part of it. I want to thank all that spend time in the community helping make everyone better.

June 6, 2012  11:33 PM

New Ignite Version

Colin Smith Colin Smith Profile: Colin Smith

We just upgraded our Ignite to version 8 and it is nice. First you will notice some nice useful widgets at the top of the home page that give you a good overview of what is currently going on. I like these but if you do not they give you the ability to hide them.

The other big thing that I noticed and LOVE, is the graphical plans and I can download the plan XML and use that in another tool if I want to. This makes it so easy to look at a plan and see what a query is really doing and what the costs are. It also gives you index recommendations, I am sure these are based on the missing index dmv so take them with a grain of salt, but still very nice.

Go check it out.

June 4, 2012  2:56 PM

PASS 2012 Sessions

Colin Smith Colin Smith Profile: Colin Smith

This week is the week that hopeful presenters for the 2012 PASS Summit will be notified if they have been selected or not. We should all know by this Friday if we have been chosen to speak, to be an alternate, or just flat out denied, like I was last year. That is OK though I am not giving up, as is evidence by me submitting again this year. I am not sure if I will be chosen this year or not. I have not had a ton of speaking experience, just three SQL Saturdays, but I am hopeful. I have had full rooms each of the three times that I have presented and have received mostly good feedback. The only not so positive feedback that I have received is that I was too monotone during the presentation. Here are some of the feedback that I have received on my speakerrate site.

“Great intro to Powershell. I just started reading up on it the week before SQLSat47 and this was a big help. It was my favorite session of the day.”

— Matt Nelson

“Colin’s presentation and content at SQL Saturday Phoenix was very well organized and interesting. He has an engaging style. I thought he’d been doing this for years until he said it was his first time teaching. Highly recommended!”


I appreciate the comments and I hope that based on feedback like this that the PASS Committee will give me a chance. We shall see and I will let you know.

May 21, 2012  4:25 PM

Strange Error when shrinking db file

Colin Smith Colin Smith Profile: Colin Smith

So I almost never say that you should shrink but sometimes you have to. I ran into such a situation recently and I continued to get this error when changing the initial size of the file.

File ID 2 of database ID 13 cannot be shrunk as it is either being shrunk by another process or is empty.

I checked and it was not being shrunk by anyone else and I know for a fact that it is not empty. So to google I went and I found this site that led me to the right path for resolution. Simple enough resolution since I did not really have the option of detaching and attaching the database. So I did as the blog author suggested and I increased the initial file size by 1 MB and then I was able to shrink it down. How that helps and thanks to Pradeep Adiga for having this solution blogged.

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: