A day in the life...

Inspired by a repost of the excellent Sean McCown's 'My Ridiculous Day' blog post, I write to present a day in the life of - well, me, at least - for anyone interested in the question - what does a DBA do all day?

Okay, so after an unexpected day off yesterday I arrive to find my inbox on fire.  One of the application support team had e-mailed me Saturday (I don't have my phone hooked up to work e-mail, so glad) regarding a replication issue.  Essentially, they had kicked off the automatic scripts I wrote to configure transactional replication, but it had all gone wrong.  Root cause?  The scripts were not only the wrong version but were in the wrong directory.  So, task one - strip out replication and redo replication, while fielding questions about how it all works.  Tick.

Back to my desk.  Next up is a backup failure for one of our clients - system code 112, out of space.  Okay.  Let's check the backup directory - wow, 95% used?  What's happened?  So I identify which database backup failed, then run the disk usage by top tables report in SSMS, to find one renegade table hogging 340GB of space at 2.9bn rows.  The hunt to find out who is responsible begins.

So, after a couple of false starts, the head of the software team shows up to chat about the table.  Apparently, there was a cron misconfiguration issue causing the task which populates the table to be run every 5 minutes instead of at 5am every month.  Fairly easy mistake to make - 0 0/5 * * * ? vs. 0 0 5 10 * ?.  So the cron issue is fixed, but this still leaves 340GB of crap to remove.  And there's no indexes save the PK, which is on a surrogate BIGINT PK.  It's too large and unwieldy to stay put, and the server isn't the TARDIS. I amend the backup retention to allow enough space for backups for a little while longer.

Two options present themselves - TRUNCATE the table (a minimally-, but not un-logged operation) which would work immediately but result in deferred drops potentially causing the transaction log to explode, or DELETE ... WHERE, which will be hideously inefficient due to lack of supplementary indexes.  Besides, the latter option would need to be done batch-wise to avoid transaction log and blocking problems, and to take an example, 6 x hourly batches of 50,000 rows removed overnight would take more than 26 years to complete.  Great.  So to find the business owner, and make it their problem.

Lunch beckons.  It's a BLT on white bread with some kind of cake and a cup of tea.  Delicious.  I sit in my car, listen to a TED podcast on why "Big Data" needs the human touch (Tricia Wang) then read an academic survey paper on machine learning that I've been studiously ignoring for days.  Make notes.  Finish tea.  Back in the office.

After lunch, I need to press on with getting access to the 30+ servers which I'm still blind to.  Given my client doesn't currently use domains or AD, all security credentials are local and stored in a central password keystore application from which I'm excluded, and it's IP address party time.  So, I write some code to create a new login (obfuscating the password) and get sysadmin.  I grab one of the IT team to piggyback upon and we start slowly logging on to each and every server as sa (I know) and running my script.  He's bored.  I'm bored.  This is not why we're here.

We're in.  Excellent.  So, to deploy some basic monitoring.  In the absence of any paid-for software I'm obliged to write my own, but in the meantime I can at least configure Database Mail and get some Sev 16-25 alerting set up.  Luckily, this is something I'm used to doing and the script's written, so just a case of deploying over and again.

This done, the effort continues to get my home-made SSRS monitoring interface finished and deployed.  I'm getting the feeling this is a labour of love, but decent monitoring is an essential part of any DBA role.  Nearly the end of the day.  I ponder whether to put half an hour into my play-2048-in-SQL pet project, but decide against it.  It's 5pm.  Time to go home.


Popular Posts