I’m at it again! I’m working on some notes about how to administer a Microsoft Database Server. I’m planning on following the fundamentals you would need to cover the 70-431 (the Microsoft Database Administrator’s exam). I’m doing this to share, but I’m also doing this to help me grow a bit stronger in my Database…
Tag: DBA
The DBA’s Rules — Rules 3 and 4
In previous posts I shared with you my rules for who does and doesn’t have access to sa, and keeping your data safe by having tested backup and restore procedures. Next I’m covering two related rules. These are to protect me as much as they are to protect you. If you’ve worked in a team…
The DBA’s Rules — Rule #2
Rule #2: Have a tested Backup and Recovery Plan I’m sure no one out there isn’t doing a backup on their database server at some interval compatible with the nature of their data. If you’re updating your data weekly, weekly is fine; if you’re updating every minute of every day, you need to backup more…
The DBA’s Rules
I don’t know if you’re a fan of NCIS, but for those of you who aren’t let me give you a little back story. Gibbs is the leader of an investigative team and he has a set of rules he teaches his team members in order to help make them better at their jobs. He…
Determine the Space Used by Tables
Eventually you’ll be faced with running out of space on a SQL server. Usually, before you get more drive space, you’re asked to find out if you can get rid of any information you’re holding onto in the server. You come up with a list of tables in databases that you feel you could remove…
Inter-Database Queries
I’ve had this question come up a few times now. “How do I copy data from one database to another?” It’s after getting a question like that, I explain fully qualified database object names. That’s a mouthful! Basically it’s a fancy way of saying the “full name” of a database object, like a table or…
Comparing Query Performance
A significant portion of your time as a database programmer or administrator will be to improve the performance of queries. While there are several benchmarks you can compare two or more queries, the actual comparision of the two queries can lean you do make false assumptions. When you run a query, data and execution plans…
Duplicates
Duplicates. I honestly can’t tell you how many times the root cause of a problem has been records in a table get repeated, where there should never be duplicates. I’ve perfected my method of removing the duplicates, but when it comes time to prevent them in the future, developers scoff. Maybe it has to do…
A Better sp_who2
If you’ve ever needed to work with sp_who2 to figure out what’s going on, then you know it would be nice to be able to sort the results, right? Well, if you use the following code, then you’ll get exactly that functionality. Enjoy! CREATE TABLE #who2 ( spid INT , [status] SYSNAME , [sid]…
Incomplete Tables
One of the things I’ve noticed while performance tuning is many of the tables in my current environment are incomplete. They all have columns defined, and they all have data, but they’re still missing something. Many are missing a primary key. You don’t always have to have a primary key, but when you’re joining against…