9 Habits of Highly Effective DBAs

Destroying data is the fear of every new DBA. DBAs are the conservative folks who have data integrity on the brain. Or at least that’s what we want people to think. The truth is that you get used to working on large amounts of crucial and sometimes sensitive data. At the start of your career, you’re cautious, but as time goes by, your skill and comfort level increases until working with sensitive data becomes easy. But still you wonder what will happen if you make a mistake and destroy data unintentionally?

You’re the DBA. You can’t ask for downtime to restore a tape because you screwed up! The confidence of your users, not to mention your boss, would be forever tarnished. So what should you do? You should develop a regimen of good database habits that keep you out of trouble and follow them no matter how good you think you’ve become. Let the rest of the world think you’re infallible. You know better. Here are nine database habits you should get into.

1. Make a Backup

Let’s start with the obvious. Make a local backup before you do anything dangerous. Save it locally so you don’t have to go looking for it in the tape library. Even if you have a regular backup schedule, it’s a good idea to make a backup manually before each major change so that you can be sure that your changes can be undone quickly.

2. Use Transactions

When you open up a query window you should always start by opening a transaction. That way, you can do whatever you need to do, check the results, and decide whether you want to keep the changes or roll them back.

To start a transaction, you can use code such as

 BEGIN TRANSACTION GoodDBA    UPDATE Account       SET Balance = 0    WHERE BalanceDate >= DATEADD(day, -1, GETDATE())

Now you’re free to see the results of your changes by running code such as

 SELECT *    FROM Account    WHERE BalanceDate >= DATEADD(day, -1, GETDATE()) 

Keep in mind that you’ll only be able to see your changes inside this connection and, thus, only this query window. If everything looks good, accept the changes and commit the transaction with the code

 COMMIT TRANSACTION GoodDBA 

If you decide you don’t like the results, reject the changes by rolling back the transaction with the code

 ROLLBACK TRANSACTION GoodDBA 

Keep in mind that holding a transaction open this way will also keep any locks acquired during that transaction open. So try to quickly determine the success of your actions.

3. Stop Highlighting

Don’t execute potentially dangerous scripts by highlighting one section at a time, especially if it would be detrimental to run a section of code more than once. You run the risk of highlighting the wrong section, running the same section of code more than once, or accidentally running the whole script at once because you fat-fingered the mouse. Instead, try commenting out the sections of code you’re not currently running. I like to place the leading /* comment characters just below the code I’m currently running and place the terminating */ comment characters at the end of the document. This placement lets me move down the document, running one statement at a time.

4. Know Your Connection

Before executing a destructive command, you should always verify that you’re executing your operation on the appropriate server and database. If your server environment is like mine, you’ve got scores of development, integration, and quality assurance (QA) servers. It would be a shame to truncate a table in production when you really meant to be working on a QA server. Also, when working with DTS packages or the Microsoft SQL Server Import/Export Wizard, always be sure that you’re selecting the proper source and destination servers. Most all DBAs have inadvertently reversed the source and destination servers at least once.

5. Stay Out of Isolation

When simply querying a database, set your transaction isolation level to READ UNCOMMITTED so that your queries don’t acquire locks against production objects. Switch back to READ COMMITTED when you plan to make any changes to the database. Keep in mind that, while in UNCOMMITTED mode, you’ll see data values that might not be permanent.

6. Stay on Top of Your Tables

If you’re like me, you would rather see some sample data when examining a new table to see how the table is being used. Running sp_help isn’t enough. And you can put a fairly high load on both the server and client machine by running SELECT * from a production-sized table, then clicking Stop in SQL Query Analyzer once enough sample data has come across the wire. Next time, try running

 SELECT TOP 100 * FROM mytable 

to minimize the amount of workload on everyone. You’ll get plenty of sample data without killing the server.

7. Stay Out of Table Designer

If you’re looking for a quick description of a table, try double-clicking the table name in Enterprise Manager instead of going into Table Designer. You’ll receive a nice read-only representation of the table schema, without having to worry about accidentally making a change.

8. Alter, Don’t Destroy

Instead of dropping and recreating a stored procedure every time you change it, consider using the ALTER PROCEDURE command to change the stored procedure in place. You don’t have to worry about losing permissions or inconveniencing users. More important, if the new version doesn’t compile, you don’t run the risk of losing the original.

9. Look Before You Leap

Next time you have to kill a SQL Server process, use DBCC INPUTBUFFER and DBCC OUTPUTBUFFER to make sure you are killing the right process. The command

 DBCC INPUTBUFFER() 

returns the current command running on the specified server process ID (SPID). The command

 DBCC OUTPUTBUFFER() 

returns the contents of the output memory buffer in both hexadecimal and ASCII format. It looks identical to DOS’s DEBUG command and gives you a good representation of what the user of that SPID sees.

Your Secret Is Safe

If you follow these nine habits, the secret of your fallibility will be safe. With a regimen of common-sense techniques and best practices, you’re free to perform god-like database operations. No one will be the wiser.