I have been told by hundreds of DBAs that I have the best DBA job in the world as Director of DBA Education for Red Gate Software. And this has been very true. In the five plus years I have been with Red Gate I have traveled over 500,000 miles (breaking the million mile record on my frequent flyer program), slept in hotel rooms for just over 450 days, and given 154 presentations to over 10,300 people in six different countries. I have written four books, hundreds of blog posts and articles, and personally met thousands of DBAs. My face has been on millions of banner ads impressions, posters, and books, and of course, last year I had the most unusual experience of my life when I “starred” in a the DBA in Space videos. Being filmed by a professional film crew and working with a real actress will probably be the most unusual thing I will have ever done in my life. The last five plus years with Red Gate has been a wonderful ride, and a great opportunity to participate as an active member of the SQL Server family.
When I was a novice DBA, I spent little time documenting my SQL Servers. But as I became more experienced, I realized the importance of having complete, up-to-date documentation for all of the SQL Servers I manage.
Some of the benefits of documentation include: 1) It is easier and quicker to find information when needed, especially if it is located in a central location; 2) Not having to reinvent the wheel every time I need to resolve a problem that I had solved in the past, but now have forgotten; 3) Being kept aware of what other DBAs are doing to the instance; 4) Helping to establish “standards” that can be used throughout the organization; 5) Additional information to help when troubleshooting problems; 6) The ability to duplicate an existing server, such as when you need to move an instance from one box to another; 7) For disaster recovery when you need to rebuild a new box to replace one that has died and can’t be rebuilt; among many other reasons.
In the April 2012 Question of the Month, I asked readers to tell me their favorite DBA books. I have compiled the results, and below are the most popular books my blog’s readers picked. If you are looking for a good SQL Server book to learn from, then you might want to consider reading one or more of them. Click on the image to find out more about the book.
There is one thing every DBA knows with certainty, and that is that databases grow with time. MDFs grow, backups grow, and it never stops. The more data we have, the more work SQL Server has to perform in order to deal with it all; whether it’s executing a query on a table with 10 million rows, or backing up a 5 TB database. Whether we like it or not, we are fighting a losing battle, and DBA’s can’t reverse the information explosion. Or can we?
I was very surprised to see the results of my latest poll, which asked “When was the last time you tuned your transaction logs?”
According to those DBAs who responded to the poll, about 60% of them said they didn’t know transaction logs could be tuned. And just over 19% said they have never tuned their transaction logs. Sadly, only about 21% said they have tuned their transaction logs in the past.
If you are one of those DBAs who don’t know transaction logs can be tuned, you need to take the time as soon as possible to learn about this topic.Why? First, every data modification made in SQL Server must be logged. Second, no transaction can complete until all of the related data modification are written to the transaction log. And third, writing to a transaction log is a single thread process. On a busy SQL Server instance, writing to the transaction log can become a bottleneck, preventing transactions from being completed on a timely basis, slowing down the entire instance.
There are many ways to tune a transaction log, some very simple, and others slightly more complicated, but it is not beyond the ability of most DBAs. To learn more about transaction log performance tuning, download my presentation slides on “Inside the SQL Server Transaction Log”, which will give you a good start on learning the basics of this important topic.
Later this year, a new book by author Grant Fritchey, called Learn SQL Server in a Month of Lunches, will be released by Manning Publications. Below is a preliminary excerpt from the book on “Creating a Full Backup”. The book is geared toward readers new to SQL Server.
Creating a Full Backup
The full backup is a complete copy of the database. It copies everything. Not just all the data, but all the tables and all the indexes and all the stored procedures; everything that makes up the database gets copied. That’s what makes this so important for protection in the event of some type of catastrophic loss. As with most everything else in SQL Server, you can perform a full backup using either the SQL Server Management Studio (SSMS) graphical user interface (GUI), or you can use Transact SQL (TSQL) to make the backups happen. Let’s start with the GUI.
Recently, I was doing some testing/playing around on a particular database on my home test servers. After I was done, I checked out the “Data/Log Files Autogrowth/Autoshrink” portion of the “Disk Usage” report for the database, which is shown below.
While I might expect to see results like this on a test box, I would be mortified if I saw it on a production box. As a small challenge to your SQL Server administration skills, just by looking at the above data, what can you deduce it? What do you think is going on, and why, and how might it affect the databases’ performance? In addition, what might you do to prevent the problem in the first place? Let’s see who can come up with the best explanation. Please post your responses below.
PS: If you are not familiar with this report, I suggest you learn more about it. It is available from SSMS by right-clicking on a database, and then selecting Reports | Standard Reports | Disk Usage. Such a simple report can make it easy to identify some specific database configuration problems, as demonstrated in the example above.