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!

Aug 242012

Sometimes, you just need to get a list of filenames into Management Studio. I typically need this when choosing files to restore for non-production backups, but there’s plenty of possible uses.

The easiest way I’ve found of getting a list of files into SSMS (or any other editor) is via the clipboard, specifically using clip.exe, found in C:\Windows\System32 on Windows 7 and Windows Server 2008.

Clip.exe allows you to pipe any cmd.exe or Powershell output directly into the clipboard, without having to copy out of the console window. For example, we have the following directory with 5 long filenames:

 Volume in drive D is Seagate 2TB
 Volume Serial Number is E6B1-FEB0

 Directory of D:\SQLBackup

24/08/2012  04:39 AM    <DIR>          .
24/08/2012  04:39 AM    <DIR>          ..
16/08/2012  09:39 PM       193,052,672 AdventureWorks_Full_20120806_1833.bak
16/08/2012  09:39 PM       193,052,672 AdventureWorks_Full_20120807_1834.bak
16/08/2012  09:39 PM        93,052,672 Northwind_Full_20120806_1830.bak
16/08/2012  09:39 PM        93,122,304 Northwind_Full_20120807_1834.bak
16/08/2012  09:39 PM        23,052,672 Pubs_Full_20120806_1833.bak
               6 File(s)    965,333,176 bytes
               2 Dir(s)  1,417,170,239,488 bytes free

By using dir’s /b (bare format) switch, and piping the results to clip.exe, we get:

dir /b | clip

Of course, you can use any filter on the dir command, to show all .bak files for a specific date (based on filename):

dir *20120806*.bak /b | clip

The results are now in the clipboard, and can be pasted into any application:


In Powershell, you can use the following (where “dir” can be replaced with gci, ls, or Get-ChildItem):

dir *20120806*.bak -name | clip

But I’m on Windows Server 2003!

If you don’t have clip.exe available, you can get similar functionality by adding one extra step – redirecting the output to a text file, from where you can easily cut and paste. Note that using the “start” command will allow you to use whichever program is set as the default for .txt files.

The cmd version is:

dir *20120806*.bak /b > tmp.txt
start tmp.txt

Powershell allows you to keep the entire contents on a single line:

dir *20120806*.bak -name > tmp.txt; start tmp.txt

Remember to go back and delete tmp.txt.

That’s it!

It’s a simple trick, but one that can be incredibly useful, and well worth a place in your toolbox.

Aug 142012

It’s T-SQL Tuesday again, and this month’s gracious host is Mike Fal (Blog | Twitter), urging us to deliver wonderful stories about SQL Server trick shots. The first thing that comes to mind is the Yo Gabba Gabba “Cool Tricks” segment. You should probably read the rest of my post before watching the video, or you’re liable to end up watching Flight of the Conchords shorts for the next twenty minutes.

My trick shot is nothing particularly revolutionary, but my hope is that at least one person hasn’t seen this trick before. I’ve used it often enough that I’ve got it down to a fine art.

The trick is using SQL Server Profiler to discover the inner workings of SQL Server Management Studio. Almost everyone knows about the “Script” button at the top of most dialog boxes to generate the T-SQL commands to perform a specific action, but occasionally you want to see exactly how SSMS came up with something it is displaying on screen. There’s nothing particularly special about SSMS – it doesn’t appear to interface with SQL Server in any way other than you or I could, using primarily T-SQL commands, with a bit of SMO thrown in. This generally means that anything you can see or do with SSMS, there will be an equivalent, and you can capture this activity.

The trick in action

For example, one of my preferred methods to see how much space is available in a file is to use the Shrink File dialog (right click the database in Object Explorer, Tasks, Shrink, Files).


There’s 220 MB allocated, and 36.75 MB available. That could be a very useful thing to know, and to use in my own set of diagnostic queries. How does SSMS determine this? Time to bring out the trick shot.

Fire up SQL Server Profiler from the Tools menu, and connect to a non-production machine. You can keep all the default events, but the important part is the filter. If you’re on a machine by yourself, a simple filter on Application Name  (like ‘%Management Studio%’) is sufficient, but you can add extra filters for your login name if necessary.


Once the trace is running, do the smallest amount of steps necessary to trigger what you want to capture, clearing the trace window if need be. For example, right clicking on a database in the Object Explorer causes 39 lines worth of activity to appear. The smallest amount would be to get into the Shrink File dialog, then change the drop down lists to choose which file you want to display. The fewer rows to wade through, the easier it will be to find that T-SQL you’re looking for. In our case:

exec sp_executesql N'use [AdventureWorks];select 
CAST(CASE s.type WHEN 2 THEN s.size * CONVERT(float,8) ELSE dfs.allocated_extent_page_count*convert(float,8) END AS float) AS [UsedSpace],
CASE s.type WHEN 2 THEN 0 ELSE @_msparam_0 - dfs.allocated_extent_page_count*convert(float,8) END AS [AvailableSpace] 
sys.filegroups AS g
inner join sys.database_files AS s on ((s.type = 2 or s.type = 0) and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
left outer join sys.dm_db_file_space_usage as dfs ON dfs.database_id = db_id() AND dfs.file_id = s.file_id
s.name = @_msparam_1 and g.data_space_id = @_msparam_2
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'225280',@_msparam_1=N'AdventureWorks2008R2_Data',@_msparam_2=N'1'

This returns a UsedSpace of 187648 KB (183.25 MB), and AvailableSpace of 37632 KB (36.75 MB), the sum of which is the 220 MB shown above. We’ve managed to discover that SSMS uses sys.dm_db_file_space_usage to determine free space.

What have I learned here?

I’ve learned many things in the past using this trick, but the most recent was while writing up this post, which was done using SQL Server 2012 (with an upgraded 2008 R2 AdventureWorks database). I originally tested using SSMS 2008 R2, connecting to a 2008 instance, and those tests showed that SSMS was actually using the FILEPROPERTY function to get the space used, with code similar to:

SELECT FILEPROPERTY('AdventureWorks2008R2_Data', 'SpaceUsed') * CONVERT(float, 8)

When I ran the test again to write it up, I was a little surprised to see sys.dm_db_file_space_usage appear. This has been available since SQL Server 2005, but this part of SSMS just hadn’t been updated to take advantage of the DMVs until now.

Parting words

There you have it – if you see any interesting value come out of SSMS, use Profiler to see exactly how they did it, and you can learn from it.

My name’s Jim, and my cool trick is using my Profiler x-ray glasses (go watch the video now).

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.