Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Database-Maintenance.aspx

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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