SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Your own personal space

Somewhere to relax, get away from it all (by all I mean pesky Developers wanting code deployed on a bleeding Friday afternoon) and generally unwind. Your worries seem to drift off until there’s a bang on the door…there’s been an issue in Production and you need to investigate. You heave yourself up, over to your desk and connect to your other personal space.

Every DBA should have their own database setup within the environment(s) that they monitor. Depending on the environment there could be one per instance monitored or one central database. Anyway, the point is to have an area specifically for yourself (and any other DBAs) so that you can store scripts for analysis and data on the instance(s) of SQL being monitored.

But what information should be captured? At a (bare) minimum I think the following information on a server should be captured:-

Backups – you want to know about your backups…….right?
Database Size – how big are you databases, how much have they grown over a set period of time?
Disk Space – how much free disk space do you have on your drives hosting SQL databases?
Index Stats – how are the indexes in the databases being used? Are they efficient?
Wait Statistics – what are the top waits on your server?

OK, so there are many third party tools out there that will collect this information (and more) for you. However, I still think a DBA should be able to collect this information by using scripts in their own script archive. By setting up these monitors yourself, you learn about the DMVs within SQL Server and how they can be used to troubleshoot a problem. You don’t want to be using them for the first time after an issue has occurred!

But what else should you be looking at? I’ve recently revamped my monitoring database to include the following:-

Auto-Growth Events – which databases are growing?
Blocking – who is being blocked? Are there any patterns?
Deadlocking – what deadlocks have occurred, what process was the victim?
Log Space – how much log space is being consumed for each database?
Suspect Pages – which pages have generated 823/824 errors?
SQL Error Log Auditing – what errors are being recorded?

Once this data is being collected, you can then decide what alerting you want to place on top. Certain events you’ll want to know about immediately (think deadlocks) but do you want to know about all auto-growth events? Maybe you do…by having your own personal space you can decide what you want to see, and tailor your space to what you need.

The DBA Who Came In From The Cold

I’m Andrew Pruski and I am a SQL Server DBA with 5 years experience in database development and administration.

The online SQL Server community has helped me out immensely throughout my career, whether from providing reference material in blog posts, or answering my (sometimes obscure) questions on forums. So, to try and say thank you, I would like to contribute my own experiences in the hope that they could benefit someone out there.

So here’s my general ramblings and thoughts about working as a SQL Server DBA.

You can find me on twitter @DBAFromTheCold

If you have any feedback on my blog please send me an email to dbafromthecold@gmail.com.


Leave a comment on the original post [dbafromthecold.wordpress.com, opens in a new window]

Loading comments...