Top Ten Things Every DBA Should Know About SQL Server

Abstract

Microsoft SQL Server has evolved over the years as a scalable, robust database management system and is now competing in the VLDB (Very Large Database) space with Oracle and IBM. The market share for the product continues to grow, based on total cost of ownership and ease of use. This white paper outlines some of the important fundamentals of Microsoft SQL Server 2008 that every DBA should know.

Sample

Top Ten Things Every DBA should know about SQL Server:

 1. A Full Backup Does NOT Truncate the Transaction Log
 2. Transaction Log Not Shrinking? OK, Shrink it Twice
 3. Yes, You Can Recover your Data Right up to the Point of Failure
 4. Data Partitioning Improves Performance for Large Tables
 5. Security - Ownership Chains Are a Good Thing
 6. Has Microsoft Deprecated your SQL Code?
 7. DTS Has Been Deprecated, Get on the SSIS Bandwagon before 2011
 8. Data Compression Improves Performance, too
 9. Change Data Capture Simplifies the Incremental Load
 10. Books Online Is Still the Best

1. A Full Backup Does NOT Truncate The Transaction Log

A frequent question that students bring to class when I am teaching is this: "My transaction log is growing out of all proportion - what can I do?" I always answer with another question "Are you backing it up?" The student usually replies, "Of course, we backup the entire database every night." "But are you backing up the LOG?" At this point the student lets me know that if we solve this dilemma, the boss will feel the cost of the class will be worth it right there and then.

Backing up the transaction log will not only backup the latest committed transactions within the log but will also truncate the log file. Truncation means that the transactions that were backed up are removed from the log file, freeing up space within the log file for new transactions. The truth is, if you don't backup the transaction log, it will continue to grow forever, until you run out of disk space. New DBAs to SQL Server assume that the Full Database Backup truncates the transaction log - it doesn't. It does take a snapshot of the transaction log at the very end, so that transactions committed during a long-running full backup are actually backed up, too (which is quite clever), but it does not truncate the log. So students come to class with a 100MB Database that has a 16GB Transaction Log. Yikes. Well, the long-term solution is to backup the transaction log frequently. This will keep the transaction log "lean and mean." But how frequent is frequent? Well, it depends. Generally, we try to keep the transaction log under 50% of the size of the data files. If it grows beyond this, then we back it up more frequently. This is why, for some very active databases, we may backup the log every 15 minutes. As I said, it depends.

This discussion assumes a database Recovery Model of "Full," which is the normal recommendation for production databases. This setting allows the backup of the transaction log so that you can recover up to the point of failure by restoring log backups in order. A Recovery Model of "Simple" will automatically truncate the transaction log periodically, but you are not allowed to backup the log in this mode, so you can only recover using the last database backup, which implies potential loss of transactions. This setting is usually reserved for test or read-only databases.

2. Transaction Log Not Shrinking? OK, Shrink It Twice

OK, so let's get back to the 16GB Transaction log file. What should we do to get this back to a reasonable size? Well, backup the log first of all. It contains some valuable updates. That will truncate the log but will not make the file any smaller. Secondly, perform a DBCC SHRINKFILE operation. Now, when we do this, we may not see any shrinkage, so let's refer to Books Online, which states, in its wisdom: "it may take two backups to actually free the space." Yes, if at first you don't succeed, try again. The actual solution is to run the BACKUP LOG/DBCC SHRINKFILE sequence twice!

The second time around, you should see significant shrinkage.

Reference: Managing the Size of the Transaction Log File (on MSDN) http://msdn.microsoft.com/en-us/library/ms365418.aspx

3. Yes, You Can Recover your Data Right up to the Point of Failure

SQL Server is architected so that you can recover ALL of the committed transactions right up to the point of failure. This fact did not go unnoticed by financial services companies on Wall Street during the early days of distributed applications - the early days of SQL Server. To these companies, even a few minutes of lost transactions can mean millions of dollars - yes, time is money. But we still need to practice disaster recovery, so that when disaster does strike, we are ready to play the hero. As a colleague of mine always says, you want to avoid any "resumé-generating events!"