I have a SQL Server 2005 database that is large in every sense. It is complex, it has a lot of data (relatively, but given the lack of blobs, it is still quite large) and it is heavily accessed.

What are the basic administration tasks that need to be done to ensure that the database keeps humming along and doesn't (worst case) get corrupted or otherwise deteriorate.

Backups are a given, but what is a good way to treat transaction logs? How long do you keep them around, how can you be sure to use them to play back from a backup if needed?

What other kinds of maintenance should be done?


Here's a great link from Kimberly Tripp'a blog. It references her Database Maintenance category. I don't want to reinvent the wheel for you here - her posts and the links from them are a great start for you.


She describes the integrity checking (CHECKDB) to do, index maintenance, transaction log maintenance, etc.


Start looking into database monitoring using SQL Profiler and Performance Monitor. These are two tools that are readily available for anybody with SQL Server installed and can give you information on how things are going.

Brent Ozar has a great tutrorial on getting started with this kind of a thing that I suggest you read.

Keep in mind that these tools tend to not be very intuitive at first. My best recommendation is to start using them as soon as possible just you you can get familiar with them. Once you get the hang of things you start to really dig down and see what's going on but for now just get to know them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.