In my 3 month DBA career so far I’ve run into 2 msdb databases exhausting drive space. While there have been SQL Agent Jobs set in place to maintain the msdb database, these don’t seem to keep the size down, even after manually purging job history via the sp_purge_jobhistory stored procedure. While I could confirm the row count was dropping, I was confused as to why the database size did not. After some investigation around the indexes in msdb I discovered a high count of ghost rows (as well high fragmentation).

Why hadn’t the ghost clean-up thread cleared these out yet? Is it because the server is continuously busy so it does not call the clean-up thread. I think I read rebuilding the index can trigger the clean-up thread. Whether this is true or not, the one time I tried it, it worked!

Now the question is, do we need to implement index maintenance jobs on system databases? Or do we accept that the msdb database may grow out of control sometimes and wait for the clean-up thread to eventually clear the ghost rows and free the space? When that happens is anybody’s guess.

This week I’m finishing up 2 of 3 one week Microsoft SQL Server courses work has sent me on. Today I learnt something new in the form of the T-SQL MERGE statement. Check it out for yourself here, what it’s capable of is pretty awesome.

I also have a heap (the fact I thought of un-indexed tables when writing this must mean I’m in SQL learning overload) of SQL Server books to read at work. At the moment I’m reading Sybex SQL Server 2008 Administration, which is an excellent read and a book I also recommend. As well as database administration tasks, it also deals with database concepts, terminology, design and planning.

After all this, I’ll be glad to go back to work and put this all to practice. I’ve already gotten my feet a little wet, but in between studies I’ve had a think about ways to improve our backup strategy as well as data integrity and high availability.

That’s all for now, live long and prosper.

As far as learning goes I’m into my 2nd reading of SQL Server 2008 Administration In Action, this time putting it all into practice and letting the theory really sink in. I have a few other books that are on their way from Amazon that I can’t wait to get stuck into as well. In 2 weeks I’ll be off on a 1 week course Maintaining a Microsoft SQL Server 2008 Database, this should really be a refresher for me and hopefully involve more lab work and possibly meeting up with other DBAs.

I’ve had a play around with the work environment. Had disk drives run out of space, troubleshoot locking issues, analyse index usage stats, looking into fixed database roles and reading up on partitioning tables. I’m lovin’ it to say the least.

I’m lucky I’m in a position where I can spend time reading the blogs or watching presentations of SQL people who I also follow on twitter (@jsauni/sql). I recently watched Brent Ozar speak on Using Perfmon and Profiler to capture performance metrics which was a great source for knowledge, tools, techniques and reasoning/understanding. Check it out if you can.

What do i set for service accounts and collation settings?

Perfect opportunity for a starting DBA to shed some newly learnt knowledge. With the current guidelines in mind and my input from what I’ve read, we agreed to this:

1. Collation: we will continue to use the collation as per the checklist to maintain consistency among ALL SQL Servers.
2. Service Accounts: we will create domain accounts for each SQL Server service being installed.

If you have any suggestions or feedback on this or would like to share your guidelines on collation and service accounts feel free to comment.

Just completed my first week as a database administrator (DBA). Aside from my first tour of the on-site server room and an issue with database files running out of space, it has involved a lot of reading. So far I’m 2 parts through a book called SQL Server 2008 Administration In Action (awesome read), with more books on the way. I already followed some SQL heads on Twitter before becoming a DBA, but have now created a list, which you can follow here.

I have 3 months to get up and running before my DBA duties kick in. From what I’ve learnt, both in reading about SQL Server 2008 and our current environment, it’s a monster of a mountain to climb. But that’s cool, I look forward to the challenge, as well as applications starting to fail after I’ve locked everything down! Kidding. Seriously though, security and implementing least privilege has been my favourite topic read thus far.