Help, I’m seeing ghosts

Not actual ghosts, but ghost rows. Before becoming a DBA I had never heard of them before. So what are ghost rows? A description I grabbed off this page here describes them as “The number of rows that are marked as deleted but not yet removed. These rows will be removed by a clean-up thread, when the server is not busy”

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.

Reading, Learning and Courses

My professional development into the DBA role I started this year has been on full blast. Almost to the point I’d say I’m border line burnt out. While it’s all been some what repetitive, repetition I find is my most effective way to learn.

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.

The 5 week DBA

Just over a month of DBA-ness and I’m still alive. What have I learned and where am I at?

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.

 

SQL Server Installation Guidelines – Collation and Service Accounts

Last Friday I received an email with the subject “default installation guidelines”. In the body it wrote:

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.