Apr 042015

Wow, it’s been just under two years since my previous post. I’ve had a couple of people ask about my blog, and why I’ve not posted. The good news is, I’m still around, and still working hard. It’s been a busy couple of years, but the highlights have included:

  • Organising SQL Saturday Melbourne (#296) in 2014
  • Organising and speaking at SQL Saturday Melbourne (#65) in 2015
  • Speaking at SQL Saturday Auckland (#186) – I can now claim to be an international speaker!
  • Working as an Oracle DBA. Although there was a pretty solid learning curve, Oracle and SQL Server are similar, and I find I enjoy both, although SQL Server still holds my heart.
  • Volunteering in the Victorian State Emergency service. While not technical, working as a storm, flood and rescue worker has been intensely rewarding.

My list of blog post ideas is currently sitting at 51 items, so there’s no shortage of content to come!

Apr 092013

If you haven’t heard, someone important died today. Someone that changed the world. No, it wasn’t a real person. SQL Server 2000’s support lifecycle finally came to a whimpering end. Not a dignified, noble, heroic, or tragic end. More of a fizzling out.

Cast your mind back, if you can, to the turn of the century. 2000. A year of renewed hope, where everyone was thankful that the Y2K bug hadn’t wiped out life as we know it. Everyone in the SQL Server world was excited about a brand new release1. SQL Server 8 was going to be fantastic, but when it exploded out onto the scene in late 2000, it was SQL Server 2000, which is a lot more exciting that 8.0. Like, 1992 better than 8. Over the next 12-24 months, SQL Server 2000 was it. Everyone loved it. Microsoft were making fantastic strides in enterprise database software, and SQL Server was finally giving Oracle a bit more pressure. The new features in 2000 were such a leap from 7.0, and made managing databases much easier.

SQL Server 2000 rocked.

It had some cracks. How can anyone forget Slammer, a worm that triggered a buffer overflow exploit on the SQL Browser service? It was relatively benign, apart from the massive amount of network traffic it generated. In theory, it could have done anything once it had gotten into the Browser. For a decent layman’s2 view, have a look at the Wired article from July 2003.

A 64 bit version was released in 2003. Reporting Services was released in 2004. It just kept getting better and better. There were hiccups – Service Pack 4 suffered a disastrous memory bug, where only half the server memory could be used by AWE.

But then SQL Server 2005 came out, and it was fantastic in comparison. It took 12-24 months to start seeing a decline in SQL Server 2000 installations, but it happened. SQL Server 2008 came out, and people started sneering at SQL Server 2000. It was primitive, hard to work with, ugly. DTS was horrible compared to SSIS. Those that had sung its praises now sighed with resignation whenever they had to use Enterprise Manager, and I’m sorry to say that I have to count myself in that group of people. “Why haven’t you upgraded from 2000 yet? It’s 2010! Way past time!” And yet, SQL Server 2000 kept chugging along, doing what it had always done, as the number of installs dwindled and dwindled.

From a very high height, SQL Server 2000 has fallen, but it has not had an end like the greatest heroes of history. Let’s spend a minute today and remember how great this product was. If SQL Servers 2005, 2008, 2008 R2 and 2012 are so great, it’s because they stood on the shoulders of a giant.3

Rest in peace, old friend.



1 Artistic liberty, because I was working with other SQL implementations back then and not paying attention to SQL Server.

2 If laymen understand assembly…

3And of course, SQL Server 2000 stood on the shoulders of 7.0, and so forth, but that fact ruins the tone of the sentence.

Apr 092013

It’s a Tuesday, and that means there’s a decent chance that there’s a T-SQL Tuesday event on today. This month’s event is brought to us by Bob Pusateri (blog|twitter), on the topic of presenting.

Like most people in the world, I never liked presenting. Who really wants to stand up in front of an audience for 15+ minutes and have people watching you… Staring… Judging… Just wanting to tell you how wrong you are? Thankfully, audiences are rarely like that – you might have one person that’s being critical, but on the whole, I find people fall into one of the following categories:

  • Interested in what you’re saying, as you know more about the topic than they do
  • Disinterested, but can’t or don’t want to leave
  • Know as much as you, but are too polite to call you out on minor mistakes

While in university, I landed a job at a Darkzone laser tag centre. I went on to write software (primarily Delphi code) for the laser tag game, which is probably as close to being a game developer as I’ll ever get. One of the main tasks in working at Zone is to perform pre-game briefings to up to 60 people, although usually the audience would be around 15 people. After performing these briefings 10-15 times each week, you completely lose the fear of standing up in front of people and telling the briefing story one more time, and it certainly helps to reassure you that if you practice what you’re presenting enough, you can easily deliver to an audience. Even if you’re making it up off the top of your head, you are still comfortable enough to stand there. That skill came in handy a couple of months ago, where I had the pleasure of making a cultural gaffe in front of 400 non-English speakers.

I’m still relatively new to presenting technical ideas in groups greater than about 5 people, but have given a number of presentations, with more planned – hello SQL Saturday 186 in Auckland! That will allow me to make the claim of being an international speaker, but the reality is that I know I have a long way to go to improve my skills. Every time I present, I go through a phase about a week beforehand, where I think “Why did I sign up for this?” I could have stayed at home, relaxing, rather than volunteering to put myself through this scrutiny – completely forgetting that most people want you to succeed. Darkzone briefings are easy – you practice the 5-10 minute talk regularly, and with no great technical accuracy. A technical presentation, however, needs to be correct (as much as possible), and due to its length, you can’t rehearse hundreds of times. At the end of the presentation, however, I’ve come through unscathed, improved myself by researching a topic well, and have imparted some knowledge to other people.

I think that’s worth it, and it’s a great feeling when someone gives you positive feedback about how they were able to use the information they gave you, even 18 months later.

My biggest tip for presenting would be to respect the audience, and put in a ton of effort in developing and rehearsing. They’re giving you an hour of their time – make it worth it, and you’ll reap the rewards.

Feb 272013

While setting up some databases into an Availability Group, a colleague asked me why, after switching to the FULL recovery model, he was getting the message “A full backup is required”, given that he was planning on using the “Full” data synchronization, which states:

Starts data synchronization by performing full database and log backups for each selected database. These databases are restored to each secondary and joined to the availability group.

The obvious question there is then “Why do we need to perform a full database backup if the Availability Groups wizard will be taking one for me?”

Looking at the script generated by the Availability Groups wizard, we can see the following steps:

:Connect SQL01


:Connect SQL02


:Connect SQL01


:Connect SQL02

RESTORE LOG [AW2012] FROM  DISK = N'\\SQL01\DataSynch\AW2012_20130213234500.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

Clearly, the full backup is a COPY_ONLY backup. From Books Online:

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.


Copy-only full backups (all recovery models)

A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

Restoring a copy-only full backup is the same as restoring any other full backup.

What Books Online doesn’t mention is whether a COPY_ONLY backup can be used as a base for the first full backup taken after switching the recovery model from SIMPLE to FULL. Whenever I find ambiguous information, I like to run a test:

-- Switch the database into the SIMPLE recovery model

-- Switch the database into the FULL recovery model

-- Failed! Msg 4214, BACKUP LOG cannot be performed because there is no current database backup.

-- Successfully took a COPY_ONLY backup

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_2.trn' 
-- Failed! Msg 4214, BACKUP LOG cannot be performed because there is no current database backup.

-- Success – full backup complete

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_3.trn'
-- Success - the log chain is initialised

We have the proof – COPY_ONLY backups will not initialise the log chain.

The bottom line is that if you skip using the wizard and use a T-SQL script to initialise the databases, you can change these COPY_ONLY backups to normal FULL backups. However, you probably should be ensuring your database is already in the FULL recovery model before you start your HA adventures. From an engineering perspective, it’s a lot easier to write a wizard that assumes you’ve already got the database in the correct state than to try and fix the state as part of the wizard.

Feb 122013

This month’s T-SQL Tuesday is brought to us by Wayne Sheffield (blog | twitter), who asks for some awesome uses of Powershell to manage SQL Server.

As with most scripts, I had a flash of inspiration that I could use Powershell to make my life a little bit easier. I spent 2 hours to save 10 seconds of effort, but it keeps saving me often. Six months ago, I needed to sort a list of server names. Nothing too exciting, and most people would fire up Excel to perform the sort.

You would first copy your unsorted list into the clipboard, paste it into Excel, highlight the rows (or the entire column) and hit “Sort”.  Then you’d select the newly sorted rows, copy it back into the clipboard, and paste it elsewhere.

That’s way too much effort.

Let’s try this workflow instead:  You copy your unsorted list. You click on an icon in your taskbar, watch a Powershell window flash up for half a second, and then you paste your clipboard, noting that the results are sorted.

The script isn’t too long, only complicated by the fact we need to hook into System.Windows.Forms to gain access to the clipboard:

## This will only work if you run Powershell with the -sta switch:
#    Powershell -sta

function Get-ClipboardText()  
    Add-Type -AssemblyName System.Windows.Forms
    $tb = New-Object System.Windows.Forms.TextBox
    $tb.Multiline = $true

function Set-ClipboardText($text)
    # Load System.Windows.Forms assembly.
    $null = [Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)

    $dataObject = New-Object windows.forms.dataobject

    # Add generated strings to data object.
    $dataObject.SetData([Windows.Forms.DataFormats]::UnicodeText, $true, $text)

    [Windows.Forms.Clipboard]::SetDataObject($dataObject, $true)

$data = Get-ClipboardText;
$data = $data.Replace("`r`n", "|||||||")
$data.Split("|||||||") | Sort-Object | Set-Variable -Name sorted

[string] $output = ""

foreach ($line in $sorted)
    if ($line -ne "") 
        $output += $line + "`r`n"

I’m sure there must be an easier way to perform the sort than my 10 line solution, but this was the best solution I was able to come up with in the limited time I allowed myself.

In order to set this up as an icon, we simply create a shortcut with the target:

powershell.exe -sta ./Sort-Clipboard.ps1

making sure to “Start In” the path with the Sort-Clipboard.ps1 file. The –sta flag is necessary to run Powershell in Single Threaded Apartment mode, in order to gain access to the Clipboard class.

If you want to use the Sort-Clipboard.ps1 script from within Powershell, you’ll need to spin up a new Powershell session in –sta mode, which can be done using a simple one-line script:

Start-Process powershell -args "-sta -noprofile .\Sort-Clipboard.ps1"

Credit and code for these techniques must go to Brian Reiter, Dave Regal, and the MSDN Powershell blogging team. Thank you all!

Server names are one example, but there’s many more situations where you might want to sort a list in day-to-day database management. You could adapt the code to sort anything – the really useful part here is the access to the clipboard.

Jan 082013

Welcome to 2013! I’m not usually one for making resolutions based on the year changing, but the topic of this month’s T-SQL Tuesday, courtesy of Jason Brimhall is on "Standing Firm", which is awfully similar to the MacLeod clan’s motto: "Hold Fast".

While I haven’t started the year with a resolution in mind, New Years day did mark the start of me riding my bicycle again after a number of years of neglect. The motivation was to get back into doing something I love – and, as usual, I was reminded how much I enjoy being on the bike as soon as I got out. However, I know from bitter experience that it’s so easy to get out of the habit. It’s too easy to make up excuses such as:

  • I have to ride early in the day due to other commitments, but I didn’t get to bed until late (my most common reason!)
  • It’s too cold/wet/windy/dark to go out.
  • There’s nothing but hills around and I want something flat.
  • How can I resolve to stay on the bike, and not fall off? (The answer is obvious – keep hold of the handlebars!)


I’ve gotten back into cycling a few times before, and it usually lasts while I have a strong reason to, for example, cycling being a more convenient method of commuting than public transport. Basically, a goal. However, what I find works well to stand firm with the resolve to ride is to spend some time thinking about what could go wrong, or what excuses I could think up, and how to handle them ahead of time. For example:

  • Choose a goal to ride for – in my case it’s getting the fitness back, trying to ride more kilometres than @woodgreg, and spending time with my cycling friends that have been awfully neglected lately. I’ll need a stronger goal soon, but that works for now.
  • My left shifter only has a short life left, and you can’t buy 9 speed replacements. Solution is to prepare early, and get a new bike ($$$), or overhaul to 10 speed ($$).
  • It’s light outside at 6am now, but soon it will be dark, and I don’t want to bother with contacts lenses at 6am, don’t want to wear normal glasses, and it’ll be too dark for my prescription sunglasses. Solution: look into prescription cycling glasses – it’s a solved problem. Do it before I need it.
  • Not enough sleep? Get to bed earlier, although I’ll probably need badgering from my wife, as there’s always something more interesting to do than sleep.
  • Too cold? Get the right gear. Too wet? Get the right gear. Too windy? Harden up a bit.

Jim, what’s this got to do with databases and standing firm?

Good question. The steps above show how my resolve is strengthened, not by force of will alone, but by putting together an environment that helps keep me on track. We can apply this to the SQL Server certification process. If the goal is to study for a specific exam, what are the possible problems that can crop up?

  • I just don’t understand this material.
  • I’m too tired to study.
  • I don’t have the right materials/equipment/practice environments.
  • There’s just too much to learn!
  • I have no interest in this – I’m just doing it because my boss wants me to.

I’m sure there’s other reasons, but you can follow the same process for any objections.

  • If you don’t understand the material, you may need someone to help you work through it. Find a friendly SQL Server person, offer them coffee in exchange for some of their time.
  • If your brain is too tired at the end of the day, consider studying during a lunch break, before starting work, or arrange with your boss to spend an hour each morning studying (and thinking how you can apply the material to your job).
  • If you feel overwhelmed by the amount of the material, break it down, and resolve to learn one subject a week.
  • If you have no interest in the material, then considering this excuse may be the reflection you need to decide whether this is actually right for you.

That’s it. Put together an environment that supports and encourages you to stick to your resolution.

I’d also recommend spending a few hours a week on a bicycle. It’s fun, it gives you a lot of exercise, and plenty of time to compose your thoughts. I think it’s no coincidence that my blog posting frequency correlates to those times when I was riding a lot!

Jun 262012

I recently posted my colour scheme for Management Studio 2005/2008, and have just completed porting it to Management Studio 2012.


As before, it’s based on a combination of Solarized and Son of Obsidian.  The most visible change from the 2008 version is the highlighting – Visual Studio 2010 uses transparency for highlighting text, which looks rather bad when  combined with my previous dark yellow colour. I’ve set it to black for now, which doesn’t look too bad. This plugin will apparently fix the transparency, but I haven’t tried as yet.

You can download the settings file from here (rename to remove the .txt extension from the file), and use the Tools –> Import and Export Settings menu in Management Studio. This also allows you to take a backup of your current settings, or revert back to defaults.

If you enjoy the settings, or have any suggestions, please let me know! I’m still using SSMS 2008 as my main IDE, so this version isn’t quite as well tested yet.

If you’re not a fan, or you’d like to enjoy other colour schemes for Visual Studio 2010, check out studiostyles (although many of the styles won’t be specifically set up for the SQL* elements in Fonts and Colors, so you may have to do some editing).

Jun 072012

It’s one of those simple things that you’re sure you should know, but are pleasantly surprised when you discover it.

The scenario was that a vendor-supplied batched audit history deletion job was running, and the transaction log was growing… and growing… The batching appears to work well from Management Studio, but not so well from the application – my guess is that the application is creating an outer transaction, preventing the inner transactions in the batch process from actually committing.

After an hour of running, the job failed, running out of log space with a 60 GB transaction log (no major issue – it was a test server), and started to rollback.

How long will this rollback take? In theory, it should be less than the hour we’d already spent – we don’t have to grow the log, and we don’t have to run through the batching process repeatedly to determine which rows to delete.

The solution? The KILL command can retrieve the status of the rollback. Normally you’d use this to see how long a killed process will take to rollback, but it will work fine for out-of-space rollbacks too.


With the result being:

spid 101: Transaction rollback in progress. Estimated rollback 
completion: 65% Estimated time left: 933 seconds.

Note that running the command repeatedly won’t make the rollback happen any faster, but it’s not likely to slow it down any either.

May 032012

I’m normally not a fan of messing with Management Studio’s default configuration unless I’m going to be using that version for a long, sustained period – I frequently use other environments that don’t allow me to have customised settings.

At the moment, I’m mainly using just two computers, and thought a change of scenery would be nice. Here’s a pretty screenshot:


There’s two main components here – font selection and colour selection. Anyone who’s used Visual Studio or Management Studio 2012 will recognise the Consolas font (11pt), which is incredibly nicer to read than the default Courier New. I believe Consolas is available with Word 2007, so most machines should have this preinstalled.

The colour scheme was originally inspired by Solarized which is incredibly well designed and thought out. I made quite a few adjustments to add a few more vibrant colours (such as the white for SQL operators, a brighter green, a brighter yellow, and a blacker background, heavily influenced by Son of Obsidian), and this has had the detrimental effect that pasting coloured text onto a white background (such as email) doesn’t work too well. The original Solarized is great in that it works well on either light or dark backgrounds.

Switching back to the default colours does significantly shock your eyes due to the brightness, so I’m enjoying the comfort of the soft colours.

To install, simply backup your [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\FontAndColors\{A27B4E24-A735-4D1D-B8E7-9716E1E3D8E0}] registry key, and load in this new set (standard disclaimer about being careful with your registry, make sure you know what you’re doing, don’t blame me, etc). You can then either restart SSMS, or go to the Tools, Options, Environements, Fonts and Colors and simply click OK.

Enjoy, and if you make any changes, please let me know – I’d be interested in trying it!

Update – August 28, 2012: Bill Hurt has sent through a slightly modified version which changes the line numbers and background colours to a deep black/brown that goes well with the rest of the colour scheme. It’s certainly worth a look! Download here.

Jul 112011

Imagine that you’ve just used SQL Server Management Studio to generate a script, and it’s done it’s job wonderfully, except for a lot of unsightly GO and blank lines:

GRANT SELECT ON [Person].[Address] TO [SomeUser];

GRANT INSERT ON [Person].[Address] TO [SomeUser];

GRANT UPDATE ON [Person].[Address] TO [SomeUser];

GRANT DELETE ON [Person].[Address] TO [SomeUser];

Although technically correct, this may offend your sense of aesthetics. If there’s only a couple of rows (as in the excerpt above, you can manually remove the unsightly GOs and blank lines, but this is a bigger ask when you have more than about 20 commands.

The simplest method I’ve come across is to use one of the DBA’s most trusted tools – Excel. Although there are other ways of doing this sort of operation, Excel is installed on almost every Windows machine, and this is a quick operation.

First, copy the entire set of commands, and paste into Excel as Column B, and in column A, add a number for each row (essentially an IDENTITY column). Remember that you can just fill out 1 and 2, select both of these, and drag down to the bottom:


This number column is used so that we know which order each row was in originally, because we’re about to sort the data. Select columns A and B for all rows, and sort by column B:


Next, select the rows you don’t want, and delete them.


Now, select the remaining data, and sort again, this time on column A, to return to the original sort order (minus the offending rows):


Copy the commands back into Management Studio, and you’re done.

GRANT SELECT ON [Person].[Address] TO [SomeUser];
GRANT INSERT ON [Person].[Address] TO [SomeUser];
GRANT UPDATE ON [Person].[Address] TO [SomeUser];
GRANT DELETE ON [Person].[Address] TO [SomeUser];