SQL Server 2000/2005

[techie08]

Hi

 

What tasks should i be doning with SQL Server to maintain the databases or manage it in general? SQL isnt my strongest point by a long way so i think nows the time to put that right!

 

Im not sure if there are routine maintainance tasks that i should be running to keep the databases running at optimal condition.

 

Many Thanks!

[powdarrmonkey]

Back it up. Apart from that, it will look after itself.

[techie08]

Thanks Powdarrmonkey. Nice and simple. The way i like it!

[vikpaw]

depending on usage levels you might want to control the rate of growth of the LDFs

[techie08]

what are LDFs?

[vikpaw]

it's a transaction log file. it accompanies the main db file the mdf.

periodically the transactions are written into the main mdf file and the ldf is truncated.

 

it usually sorts itself out quite well, but there can be instances when it doesn't truncate well, or if the volume of transactions is really high, it can grow too quickly.

 

there are options to have it increase by a fixed percentage e.g. 10% or to increase by a fixed amount of disk space e.g. 100MB. i prefer the latter. it grows to that fixed size and then any space within is used up until it is either truncated or needs to grow again.

 

we had this as a massive problem on our school's SIMS database once because the backups weren't correctly consolidating the data.

[robins]

You can opt to record log dumps to enable reverting back to perhaps hourly logs of data conditions or indeed to precisely return to an individual point in time. Usually this level of recovery is more than schools really need.

To aid performance you could also...

reindex tables (both in your own databases and the system databases master and msdb).

update statistics on stored procedures (help bits of code run at optimal speed.

shrink database device files.

The scheduled job wizards offer these options and more - worth experimenting with out of hours though can starve other services without giving any great benefit.