SQL 2000 Maintenance Plans

[user35]

Is there a recommended approach for a SQL Server 2000 Maintenance Plan in a Macola environment? Should I reorganize data pages? if so, what percentages? Should I check database integrity? Include indices? The Backup and Trans logs settings are pretty straight forward.. Just wondering what if there was a recommended practice on how to handle the data optimization and integrity check portions. Thanks!

 

[abaldwin]

Depends on what you want to do.

My basics are this

1. Every day backup the database only.
2. Every 1 hour back the transaction logs only.
3. On Sunday do the db maintenance stuff.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[stravis]

Andy's basics are a good starting point, but as he says it depends on what you want to do and what your requirements are. If you have processes that create lots of transaction log activity then you want to increase the frequency of transaction log backups. You want the transaction log to never grow over 100MB max. This rule applies even to large multi terabyte databases. With large multi terabyte databases the transaction log is backed up every 2-3 seconds to prevent log growth.

It is best to talk to a SQL professional about your individual needs so that the process is tailored to your requirements and the amount of space you have available on your SQL Server.

Scott Travis

http://www.infospring.net