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.

Continue reading →

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.

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.

Continue reading →

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.

Continue reading →