Archive for January, 2010

SQL Fu – growing!

Tuesday, January 5th, 2010

Ok – so I am trying new things over here at the new job. Something I have steered pretty clear of in late is SQL. Be it mysql, mssql or postgresql – I am out of my depth. The greatest extent of my powers is installing and setting up a wordpress database. Simple stuff. show databases; use wordpress; That kind of magic. Nothing special.

Nearly all of our clients run some form of database – with what I imagine 98%+ being SQL. So there are plenty of things to learn around here. I’ve managed to grab time playing around with SQL Enterprise manager from a MSSQL point of view – things are good there… GUI so its straight forward enough to run maintenance plans and all that. I’m sure there are a ton of things above my realm but I’ll keep going.

My latest foray with SQL was with a client VM with limited space and numerous databases. Something had to give – we were dumping backups from SQL for 3 days before ageing them. Backups occur nightly so we had a massive overlap here. Essentially the 1+ days of SQL backups (that were already backed up to our master server) were consuming more and more space as the databases grew with the client needs.

With enough playing around I had written a little t-sql statement that took a backup of the main databases (excluding stage and dev which were consuming needless space) and then removed any database files that were older than 1 day – effectively cleaning up after itself. This gave us a massive win – recovering 2 thirds of the space that was being consumed by aged database backups.

We were happy – stopped receiving alerts for disk utilisation. Client was happy – disk not filling up!

All in all, a good dip in to the land of SQL.