The Multifunctioning DBA

October 30, 2010  12:00 PM

Delete in Batch

Colin Smith Colin Smith Profile: Colin Smith

Today I needed to delete 28 Million Rows from a table and I did not want to do it as one large statement because that would kill my log.

Here is what I did.

–Set your rows to deal with
DECLARE @intloop int

Select @intloop = count(*) from Table
where Column < Something
While @intloop > 0

— how many to do in a batch
SET rowcount 500000

–do the delete
delete from Table  where Something < Something

set rowcount 0
–decrement intLoop
SET @intLoop = @intLoop – 500000

print ‘Batch Complete’
print @intLoop

October 29, 2010  12:00 PM

PASS Streaming Key Notes

Colin Smith Colin Smith Profile: Colin Smith

If you can not get to the SUMMIT this year you may want to watch the streaming keynotes. PASS is awesome to do this and if I was not going to be there in person I would be watching. So here is a link to register to watch.

October 29, 2010  8:00 AM

Fun with SSIS

Colin Smith Colin Smith Profile: Colin Smith

So I am working on a project and in this project I am getting more and more exposure to SSIS. This is fun but can be frustrating. I am working on setting up configuration files for connection strings for all the packages. This is pretty simple in BIDS to set up but when I want to move the files to another environment and just edit the file to point to the correct datasources it gets a bit hairy. I think a big part of that is that I do not really know much about XML and that is the type of config doc we are using. I need to learn XML now as well. It never never ends.

October 28, 2010  11:00 PM

SQL Server Monitoring

Colin Smith Colin Smith Profile: Colin Smith

I am getting back into writing my monitoring application in powershell and I have made some progress over the last few days. I have figured out my issue with getting the backup dates and times inserted into my monitoring database and also not entering the information multiple times if I already have an entry for that day for the full backup if the database is using the simple recovery model.

Then I wanted to develop a new script that would allow me to add a new instance to monitoring very quick and easy. It took me about 40 lines of ode but man it really does work great. The script will ask for some simple inputs like hostname, instance name, do you want to monitor it, who is the Customer, Test or Prod Instance, so really everything in the monitor and instance tables. Then it goes and inserts the data in the appropriate tables and also gets the instance identifier from the instances table before inserting it into the monitor table.

Getting ready to put this in place with the basic functionality and then add features as I go.

October 28, 2010  9:58 PM

Powershell change color of text

Colin Smith Colin Smith Profile: Colin Smith

I am working on a script that accepts some user inputs and then echos that back to the user for validation. For this I wanted to make the text that the user submitted be in RED so that it would stand out and make it more readable.

I was looking for a way to use this doing echo and echo is an alias to write-output. I was disappointed to find that write-output could not change the color of text. So after some digging I found write-host and what do you know, it has a -forgroundcolor and a -background color option as well as one that was new to me called -nonewline. So below is what I did.

write-host “This is in white” -nonewline; write-host ” and this is in red” -foregroundcolor red -nonewline; write-host ” and now back to white”

Give it a try

October 22, 2010  10:00 AM

Create Random Password and update AD account

Colin Smith Colin Smith Profile: Colin Smith

Not to long ago I was asked to write a script that would create a random password and change an AD account to use that new password. The requirements were that the password be 3 uppercase letters 2 numbers and 3 lowercase letters. Here is what I did.

#Clear out all variables from last run

$newpassword = $NULL

$loops = 1..3

## Set up Random Generator

$rand = New-Object System.Random

$i = 1

$n = 1

#set up loop to generate each section of $NewPassword

foreach($number in $loops)


if ($i -eq 1)


$part = $NULL

$m = 3

$s = 65

$e = 90


if ($i -eq 2)


$part = $NULL

$m = 2

$s = 48

$e = 57


if ($i -eq 3)


$part = $NULL

$m = 3

$s = 97

$e = 122


$n..$m | foreach { $part = $part + [char]$$s,$e) }

$newpassword = $newpassword + $part

$i = $i + 1

Set-QADUser PNI-GUEST -UserPassword $newpassword

October 21, 2010  4:30 PM

PASS Summit 2010

Colin Smith Colin Smith Profile: Colin Smith

Now with just about 2 weeks left before the big summit I am getting very excited. I have received my hotel confirmation and I also booked my flights last week so I should be ready to go. Also, I have been looking at all the sessions and all I can say is that I need more of me so I can be in multiple places at once.  So much good information is going to be handed out that I am afraid that I will not be able to suck it all in. I did not register for any pre or post con stuff and I wish I would have. Lesson learned for next year. I am sure that I will be purchasing the DVD of all the sessions though. I have set up a schedule for the sessions that I would like to attend most. WOW I have zero time on my schedule to just enjoy Seattle. This is my first trip to Seattle and I am looking forward to it and I really hope to meet some of you and hang out and have a good time. I am so looking forward to it that I just can not wait.

Hope to see you there.

October 21, 2010  10:00 AM

Last Month

Colin Smith Colin Smith Profile: Colin Smith

I must apologize for my lack of blogging last month. I was busy moving and the house that I purchased has required a large amount of TLC. I think that my wife and I have it livable now. We had  to redo some of the flooring, we went with stained concrete and love it so far, and we have had to do a ton of cleaning and painting. I also decided that since I stream almost all of my TV from the internet that I would run wire to every room in the house and multiple connections in to the TV room wall and the bedroom wall with the entertainment center. After I am done I will have 22 drops in the house. I might even disable my wireless after I am done. So far I have a few done and I also bought a Gigabit switch to go with my Gig Router. No more skipping or lag problems when streaming local or internet HD Content to the TV.

So now that the house work has settled a bit I will be back online.

October 20, 2010  7:30 PM

Fantasy Teams

Colin Smith Colin Smith Profile: Colin Smith

A while ago I mentioned that I was doing a fantasy football league for the first time. It has been fun but I have found that I am not good at it at all. I am in 11th place in the league right now and that is out of only 12 teams. I have only won one game and that is very very sad.

Well now I have decided to try my luck again but this time at a game that I know better than I know football. My IT department has but together a new NBA Fantasy league and the draft is this afternoon. I have the 8th pick again out of 12 teams. I hope to pick up a few of my top rated picks but we will have to wait and see. I hope to do better in this league then I have been doing in the football league. Should be fun..

Wish me Luck

October 20, 2010  4:16 PM

SQL Server Agent Not Starting

Colin Smith Colin Smith Profile: Colin Smith

This week I ran into a strange issue where one of my servers agent accounts would not start up. The SQL Server and the SQL Agent both use the same domain account and the Server was running just fine. The error in the event viewer was a bit strange. It was the following error:

SQLServerAgent could not be started (reason: Unable to connect to server ‘Some Strange Name’; SQLServerAgent cannot start).

After looking into this I found that someone had modified the SQL Server Agent connection properties withing SSMS and had put the ‘Some Strange Name’ into the Alias Local Host Server field.  After looking up what that field does,, I decided that I did not have to have anything in that field. I removed the entry, hit OK and then tried to start the service and what do you know. It all works now.

Hope that helps if anyone else is having issues like that.

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: