Maintenance Best Practice

[Imashe]

I have recently enabled maintenance jobs on our SQL 2000 server. I am wondering what "best practice" is for how often to schedule the data integrity check and optimizations? Currently, our database is about 3.5GB. Any advice? Any good SQL links anyone would like to share with a rookie DBA?

Thanks in advance.....


"The worst dilemmas are often the best opportunities in disguise."
'Carpe Diem'

 

[JayKusch]

You probably could get away with running the checks every week or so. I usually set mine to run during late night weekend downtime.

Now if some of your DBs are heavily used, ie Insert/Updates/Deletes ... you may want to do them more frequently.



Thanks

J. Kusch

 

[nigelrivett]

I never use maintenance plans but code whatever I need in SPs.
I also don't run checks on the live databases but restore a backup and run the checks on that (usually daily - however often the test restore is scheduled for).
This also checks that the backup is good.
I always run automatic restores of backups - daily if possible but less often if time/space don't permit it.

For optimisation I try to design so that it isn't needed - for transactional systems this often isn't possible but it will depend on the system how often this is required.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.