Friday, 31 December 2010

Social Networks for the DBA


There are a lot of social networks about.  I gave up Facebook because I got fed up of people throwing sheep at me.  I managed to solve one Facebook mystery – the reason that all these gorgeous Russian babes wanted to be my friend.  If you look up Jack Whittaker on Google you will find that Jack Whittaker of Culloden, West Virginia won $300 million of the state lottery – he even looks a bit like me! 

Babe Magnet

I can’t get on with Twitter – I don’t have anything to say to the world in less than 140 characters.  Neither do most Twitter users, but they just say it anyway. 

So – LinkedIn, then.  It’s rather more adult than most social networks.  The idea is that you can connect to other professional people, learn what they do, give and get advice on serious and not-so-serious subjects, look for jobs, maybe find potential new members of staff. 

Start with your profile.  Click here to see mine:

Essentially it’s your CV online – what you do, where you work, how long you have been doing it.  You can say as much or as little as you want, you can put all your past jobs in there in or just your current one.  Make sure you say the key words that matter, as this is what people look for.  For example, if you want people to know that you are familiar with SQL Server 2008, make sure it’s in your profile, or your Specialties (sic - sorry about the American spelling). 

Then you want to make connections.  Start with the people you work with – if they are already on LinkedIn, you can send them a request.  Most people are happy to connect to you.  I have 20 or so pending requests - possibly people who wish to deny knowing me, but I suspect really they don't look at Linkedin very often.

Here's some advice that I found useful - it's ten mistakes, so just do the opposite!

Here’s why – suppose I was an employment agency, looking for a French speaking DBA in Bristol.  Select Advanced Search, put in the keywords DBA and French, put in the location, and it finds 30 DBAs for me with at least some knowledge of the language.  I can look at those people’s CVs, connect to them, send them a message if I want.  And it’s free.  Now that’s much more attractive to an agency than paying 20 grand a year to access the job sites. 

From my point of view, the more connections I have, the easier it is for them to find me, and the closer I am to the top of the list.  I have 200 or so connections; those people in turn know 40,000 other people; and the 40,000 know 3,000,000 or more.  So I am three degrees of separation away from all those people.  I may not know the man in Mauritius at the other end of the chain, but I know someone who knows someone who knows him personally – and that means I can get in touch with him directly if I want to.  And if he happens to be looking for a French-speaking DBA to work on a tropical island paradise, he can get in touch with me. 

Helpful hint - if you ever have anything to do with an employment agency, always send them a request to link in - they usually have several hundred contacts.

The other thing that is worth doing is to join groups which are related to your professional interests - so for example, go to groups, type in Qualitative Data Analysis, and it finds three groups.  Join up, read the discussions, contribute.  If someone sounds sensible, tell them so and ask to link to them. 

We were arguing in the office about whether it's worth rebooting a Server on a regular planned preventative maintenance - some said yes, others took the view that if it ain't broke, don't mess with it.  I started a discussion on one of the many DBA groups and pretty soon we had a consensus - leave well alone. 

Remember that you are marketing here, rather than selling - you want agencies to find you and it is easier for them to do so if you know lots of people in the field.  Try to avoid the direct sell - there's a guy who posts on one of my groups to say that he is looking for a job but never posts otherwise - I get the impression that he is posting everywhere, essentially spamming us. 

As well as that, agencies (and even some companies) post jobs on these groups - if a relevant one comes up, great, apply.  But if you see something that isn't relevant, it might interest one of your contacts.  Pass it on - say "It's not really suitable for me, I'm looking for X rather than Y, but I thought you might be interested" 

Think about where that gets you.  Someone in your field now knows what you are looking for.  They feel well-disposed towards you, even if they are not actually job hunting.  If they see X, there's a pretty good chance that they will tell you about it.  If an agency approaches them, there's a good chance that they will refer you. 

So you can market yourself online, you can learn stuff and get technical questions answered, you can have fun.  What’s not to like?

Happy New Year!

Wednesday, 8 December 2010

Job Hunting

OK, so it's a bit off to one side of the other things I have posted on this blog so far.  But I am job-hunting, and no doubt others are too, so someone out there might find it useful.  There seem to be more websites advertising jobs than there are jobs.  Here's my list. 

If you get your CV uploaded, then employment agencies really do trawl through these sites and they really do ring or email you.  But although it's free to you, it isn't free to them - it costs an agency £10,000 a year or more for just one of these sites - so agencies only sign up for one or two.  Hence you need to load your CV on at least a few of these. 

Set up alerts.  This seems to be a scraper site - it picks up the jobs from lots of other sites and delivers them to you in one long email.  I set up an alert for DBA which delivers a manageable list every day.  But add the magic word SQL, and it delivers far too many - so I have to put up with the Oracle jobs too. 

Set up alerts; Upload CV - an old reliable favourite

Set up alerts; Upload CV - another old reliable one

Computer Weekly
Set up alerts; Upload CV

Career Builder
set up alerts; upload CV

CV Library
Set up alerts; Upload CV
Bizarrely, they sent me a list of jobs I might like today, including a Drama Teacher in Mansfield, which is a bit odd since I told them I was looking for a SQL DBA post in Bristol <sigh>

Set up alerts; Upload CV
Thanks to Richard for spotting that I was linking to the US site
CW Jobs
Set Up Alerts; Upload CV

IT Job Board
Set Up Alerts; Upload CV

Planet Recruit
Set Up Alerts; Upload CV


Set Up Alerts
Slightly cockeyed – if you edit your alert, and click on the Save Changes button, it unsubscribes your alert.  There is a Save Changes button and an Unsubscribe button, and someone has screwed up.  It’s easy to work around – just subscribe again – but you need to be aware of it.  I’ve emailed them to report the bug – let’s see if they fix it before you read this!

Total Jobs
Set Up Alerts; Upload CV
I think this is linked to one of the other job sites here.  I went to load my up to date CV and it was already there, but the rest of the entries related to things I was applying for five years ago.  A little bit spooky...

No Good for Me

Jobs in the Public Sector and Charities
Nothing for the DBA here – only one job in the whole country so much as mentions SQL.  Clearly IT employers in this sector shop elsewhere

Job Centre Plus
Have you ever heard of anyone going to the Job centre and coming away with a job?  I did hear of it happening once, but that was in France with their ANPE organisation.  You are welcome to try – but I put in SQL Server DBA and it came back with a couple of Data Input Clerk vacancies

Empty Lemon
Great name!  It's an anagram of Employment, geddit?  
Great logo - it's an empty lemon!  Ha ha!
Shame it never has any DBA jobs...

Still to sign up for:






I haven't bothered with these as I don't think you will find many DBA jobs in The Guardian and the other nationals.  But it doesn't hurt to list them, as what is true for me may not be true for you.  

MyJobGroup Network - e.g.

Guardian Jobs

Independent (

Telegraph Jobs

Times Online

London Jobs


Just London Jobs

Good Luck!

Friday, 3 December 2010

Extracting Different Bits Of A Single Field

So what do you do when some crazy person designs a database with a single field to hold three separate bits of data?  Of course it probably wasn’t designed as such – probably somebody using the database decided that it would be neat if they could record three bits of data about the incoming call.  Rather than asking the developers to add a couple of extra fields, some bright spark decided to use the Category field to contain two or three things. 

So – let me show you what I mean. 

Audit Approach - Audit Approach Manual - Other
NHS - Other NHS
NHS - Audit Responsibilities
NHS - Audit Responsibilities
NHS - Accounting - Financial instruments
Local Government - Housing Benefits
Local Government - Housing Benefits
Local Government - Housing Benefits
NHS - Audit Responsibilities
NHS - Audit Responsibilities
Local Government - IFRS Conversion - PPE and other non-current assets

So sometimes there are two categories, and sometimes three.  Fortunately they always use a hyphen between them. 

But now I have to create a report which reads in this single field and splits it into three – before the first hyphen, between the hyphens, and after the second hyphen (if there is one). 

Category 1

So, the first bit is easy:

       --the original single field
       -- cat 1 - before first hyphen
       substring(ca_full_name, 1, charindex(' - ', ca_full_name) )  
as Category_1,

SubString() lets you read in a text field, select a start point, then select a finish point.  So if your text says “Antidisestablishmentarianism” you can say

select substring ('Antidisestablishmentarianism', 1,4)

and get the result “Anti”. 

But in this case we don’t know where to stop, so instead of specifying the end point as a number, we have to use the Charindex() function. 

select charindex ('i', 'Antidisestablishmentarianism')

This means look through the text until you find the first letter ‘i’. 
The answer is 4

So the following code says look through the name until you find space hyphen space

charindex(' - ', ca_full_name)

Okay so far?

Category 2

Here’s how to get the middle bit.  I’m indebted to Stephen Lasham, who lives in New Zealand and wrote this article back in 2004 (isn’t the Internet wonderful?).  The trouble with Charindex() is that it only finds the first occurrence of the thing you are looking for. 

Extracting a string from between two delimiting characters

       -- cat 2 - from first hyphen to second hyphen or end
       -- when no second hyphen,
       WHEN CHARINDEX(' - ',(SUBSTRING(ca_full_name,
CHARINDEX(' - ',ca_full_name,1)+1,99))) = 0
       -- then extract from first hyphen to the max length of 99
       THEN LTRIM(RTRIM(SUBSTRING(ca_full_name,
CHARINDEX(' - ',ca_full_name,1)+3,99)))
       -- else extract from the first hyphen up to second hyphen
       /* <text>> */ ca_full_name,
       /* <start > */      CHARINDEX(' - ',ca_full_name,1) + 3,
       /* <length> */      CHARINDEX(' - ', SUBSTRING(ca_full_name,
                            CHARINDEX(' - ', ca_full_name, 1)
+1,    99)) - 2 ) ) )
       END    AS Category_2,

So this time, there are two steps.  If there isn’t a second hyphen i.e. there are only two categories in the field, start not at the beginning but at the first hyphen, then read to the end.  The Ltrim() and Rtrim() bits get rid of any leading or trailing spaces, so it looks nice. 

If there IS a second hyphen, Stephen’s code starts at the first hyphen, then reads to the second hyphen. 

Category 3

Okay, nearly there. 

--  cat 3 - after second hyphen (if present)
       -- when no second hyphen,
       WHEN CHARINDEX(' - ',(SUBSTRING(ca_full_name,
CHARINDEX(' - ',ca_full_name,1)+1,99))) = 0
       -- then print nothing
       THEN (SUBSTRING(ca_full_name,0,0))
       -- read back from the end until you get to the hyphen
       reverse(substring(reverse(ca_full_name), 1,
charindex(' - ', reverse(ca_full_name))-1))
       END                   as Category_3

So, if there isn’t a second hyphen, then print nothing at all (start at 0, stop at 0).  Because there isn’t any third bit, see?

But if there is a third bit, what I’ve done is read it from the end using the Reverse() function

select reverse ('Antidisestablishmentarianism')

Which of course gives us msinairatnemhsilbatsesiditnA. 

The code reads in the text, reverses it, reads through it until it gets to the hyphen.  Then it takes the backwards text up to that point and reverses it again so that it comes out facing the right way.  So we end up with this:

Local Government
Whole of Government Accounts
Accounts and Accounting
Service concessions and leases
IFRS Conversion
Employee benefits
Local Government
Capital Finance
Financial Instruments
Local Government
Operational and Legal Environment

Foundation Trusts
Additional Services
Non-Audit Services
Foundation Trusts
Service concessions and leases
Audit Responsibilities

Local Government
IFRS Conversion
IFRS conversion
Local Government
Operational and Legal Environment

All done!  I hope this is useful.  And just think – none of this would have been necessary with a better database design in the first place. 

Wednesday, 27 October 2010

Task Scheduler for Windows Server 2003

Task Scheduler lets you automate tasks, and if you are lucky enough to have a Windows Server 2003 operating system, you get a simple to use, simple to understand wizard which helps you set it up.  

Woe betide you if you use Windows Server 2008 though… 
I'll do a write-up on that one when I get a chance. 

Click on /Start
/Control Panel
/ Scheduled Tasks
/Add Scheduled Task
And up pops the wizard.

Like it says, click on Next.

It will show you a list of programs that you may or may not want to run.  Scroll down the list and discover that the one you want isn’t there.  Now click on Browse.

I put my script in a folder which I call C:\Scripts.  Cunning, eh?  No-one will ever think of looking there.  Select the script and click on Open

 You can give your scheduled task a name, but it cleverly assumes that you will want to call it by the name of the program or script you selected.  Tell it how often you want to run this. 

And what time and what day you want to run it.

Then put in the name and password of a user who has the rights to do stuff.  You could enter your own name, but that will cause your successor a problem when you leave the company and everything stops working.  You might think that once you have gone you no longer care, but you never know if you will end up applying for a job to someone who remembers you. 

Hooray!  You’ve finished!  Easy wasn’t it?  One last thing to check – click the advanced options box and then Finish. 

This is the box you get if you want to edit your job, or if you set things up without the wizard.  It isn’t particularly advanced at all, if you ask me – the first two tabs just cover the ground that the wizard led you through.  But I think it’s worth stopping a task if it hasn’t succeeded after an hour, or whatever you think is a reasonable time, depending on what you want to do.  If it hasn’t succeeded after running for an hour, it probably isn’t going to no matter how long you leave it. 

Hope you find this useful.  I'll let you know when I've done the 2008 version. 

Monday, 18 October 2010

Maintenance Plan Log Files Won't Die

When you run a maintenance plan in SQL Server 2005 – to take backups or databases and transaction logs, to check integrity or reorganise indexes – it creates a log of all the steps it has gone through.  If you have no life, you can read these logs every day.  But the chances are that you never will, unless you need to investigate a problem. 

The maintenance plans in SQL Server 2005 onwards have a Maintenance Cleanup Task, which lets you clear out old backups and old log files.  The backups will fill your disk pretty quickly if you don’t clear them out, but the log files only take up a couple of hundred kilobytes so on most servers it’s not a big deal.  But I like to keep things tidy, so I’ve recently gone through my servers (I have about 30) adding a cleanup task to get rid of the oldest ones every week. 

Here’s my Cleanup task for old backups:

The trick here is that the file extension must be “bak” and NOT “.bak” – put in that dot, and it won’t work.  Leave it out and it works beautifully, so thank you Mr Gates. 

Now - here’s my Cleanup task for old log files

Notice that I am deleting text reports rather than backup files, and the file extension is “txt” not “bak”.  Apart from that it is pretty much the same.  Except – sorry Mr Gates – it doesn’t always work. 

The maintenance plan runs, and reports success.  Here’s the underlying SQL (just press the View T_SQL button):

EXECUTE master.dbo.xp_delete_file
N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\',
Run the code, and it tells you:
Command(s) completed successfully.

Very reassuring.  Except that sometimes the old log files remain in their directory, smiling up at you in disdain.  Do your worst, they seem to say, see if we care. 

Google has lots of entries from people struggling to get the maintenance plan to work.  Most of them are adding “.bak” in the file extension.  Take out the dot and the problem goes away.  Pfft – easy peasy.  But that doesn’t help with text files. 

Let’s consult Microsoft – and see what the Documentation of xp_delete_file says.  Here we are:

It’s super-helpful – I’ll quote it in its entirety:

Posted by Microsoft on 23/09/2005 at 18:28
Thank you for contacting us about this. I have checked with the feature owner. This extended stored procedure is undocumented by design.

Great – thanks Bill!  That’s a great bit of design.  I must remember that one when my manager asks me to document my code. 

But Google is my friend.  After a lot of searching I found another entry:
This says it doesn't work on files where the first line of the report reads “NEW COMPONENT OUTPUT”.  And why, pray, does it say this in line 1?  Because SQL Server tells it to, of course.  Delete this, and it works!  So all I need to do is write a script to go through all my daily log files, deleting the words “NEW COMPONENT OUTPUT” from the first line, and run my maintenance plan clean up task again. 
Let me be fair to Microsoft – they have fixed it now.  There is a hotfix for this in 2005 sp2 (, and it works in sp3 and SQL Server 2008. 
Serves me right for using software that can’t be migrated to the latest version!
OK, let us deploy the alternative cat-skinning apparatus.  Ever heard of Forfiles?  It’s a batch command for doing things to files.  More info here:

So what I do is set up a batch file in c:\scripts on my server called something like Delete_Old_Log_Files.bat, and set up a scheduled task to run it once a week . 

First step is to navigate to the location where the text files live.  If my log files lived somewhere like D:\Logs, I could use the path option in Forfiles: -p D:\Logs.  But sadly Forfiles doesn’t like spaces in directory names, and of course I have spaces in my directory names (and so do you, probably, if you use SQL Server’s defaults).    

I could try –p d:\ -s, which tells it to work through all the subdirectories on Drive D, deleting old text files like Genghis Khan sweeping over the steppes, and slaughtering all in his path.  Call me namby pamby if you must, but I’m a bit scared of the Mongolian approach to database administration. 

So I put this in my batch file:
cd\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

Then the forfiles command itself:
forfiles -m *.txt  -d -28  -c "cmd /C del @FILE"

-m is the search Mask – it says that I am interested in all the files with a txt suffix. 

-d is the number of Days before the current date – so I’m saying find me the files more than four weeks old. 

-c says run the Command in quotes (to delete those selected elderly txt files)

The whole batch file looks like this:

rem This batch file is set to run weekly
rem  It removes SQL Server log files >28 days old
rem Jack Whittaker 14/04/2010

rem navigate to the right location
cd\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
rem delete all text files > 28 days old
forfiles -m *.txt  -d -28  -c "cmd /C del @FILE"

So if you were wondering why your log files were staying in place, you now know why, and what to do about it.  I hope this has helped.