I have always recommended that DBAs be as proactive as possible, catching potential problems, and actual problems, as soon as possible before they can negatively affect a SQL Server’s performance or availability. In fact, I am working on a new book, tentatively titled, How to Perform a SQL Server Health Check, which will provide a large number of check-off lists DBAs can use to verify that their SQL Server instances are correctly configured using generally accepted SQL Server best practices.
SQL Server 2008 R2 Best Practices Analyzer
A big part of my DBA career has centered around identifying and sharing SQL Server DBA best practices. There are literally hundreds of different best practices, and as you might expect, not every best practice applies to every SQL Server environment. That means, that as a DBA, you must evaluate the many commonly accepted best practices and determine which ones best meet your particular needs.
How Often Do You Backup Your Transaction Logs?
Whenever I speak about database maintenance, I always recommend that DBAs backup up their production database transaction logs at least once an hour. As is with most things DBA-related, there is no exact answer that will meet every SQL Server environment’s needs. The interval between transaction log backups depend on many factors, including, but not limited, to how much data the organization is willing to loose, how active the database is, and so on.
When I set up database maintenance plans myself, I generally backup at 15 minute intervals, although sometimes I deviate from this, depending on a variety of different factors.
In my most recent poll, I asked DBAs how often they backup their transaction logs, and the results are shown at the left.
It is interesting to see the various choices made by the various DBAs who completed the poll. Based on my recommendation of backing up transaction logs at least once an hour, (if you add up the results), nearly 77% follow this recommendation, which I think is great.
A few other DBAs choose different transaction log periods. Just over 2% of the DBAs said they never back up their transaction log. I hope this is because their databases use the Simple Recovery Model, and not because they don’t know what they are doing.
A surprising 6% or so said that they don’t even know what a transaction log backup is. Hopefully, after seeing this post, they will do some research and find out why transaction log backups are so important.
How Often Do You Run CHECKDB Against Your Databases?
I recently ran a poll, asking DBAs how often they ran DBCC CHECKDB against their databases. Compared to some of the polls I have run recently, I was somewhat pleasantly surprised by the results.
My first pleasant surprise was that at least 90% of those polled knew what DBCC CHECKDB was. This is a good sign that most DBAs have at least a basic understanding of database maintenance best practices.
Of the 90% of the DBAs who run DBCC CHECKDB against their databases, just about 27% of them do it on a daily basis. These must be very diligent DBAs who take their roles of “protectors of the organization’s data” very seriously, and I commend them.
When Times are Tough, Do You Rise to the Occasion?
This is a reprint of my editorial in Database Weekly.
Scenario One: The new third-party application, purchased by your company without your involvement, is performing poorly on SQL Server. As the DBA, you are getting a lot of flak from users and management, but your hands are severely tied as to what you can do to fix the problem.
Scenario Two: The IT budget has been cut, one DBA position has been lost, and the money promised to replace your aging hardware is nowhere to be seen. And, by the way, you now have to take over all the duties that the laid off DBA used to do.
When faced with difficult times such as these, what do you do? Do you:
SQL Server Database Maintenance Checklist
In this blog post, I continue my series of SQL Server Health Checklists that I am developing for a new book I am writing. The focus of this checklist is database maintenance.
Database maintenance includes a defined set of proactive tasks that a DBA needs to perform on a periodic basis to help ensure that their databases perform optimally and maintain high availability. In many cases, the individual maintenance steps taken might not, by themselves, seem all that important, but the accumulative effect of performing database maintenance tasks can be huge.
Brad M McGehee 