Link to home
Start Free TrialLog in
Avatar of isdd2000
isdd2000

asked on

Scheduling SQL Maintenance Tasks

Hi Experts,

I am wanting to perform regular maintenance on our new SQL server but am unsure as to what tasks are beneficial, how often to run them & how to schedule them so they are not manual tasks.

Any feedback is welcomed, look forward to reading your comments.
Avatar of Patrick Bogers
Patrick Bogers
Flag of Netherlands image

Hi,

SQL server doesnt require much maintenance, tasks to schedule are more user defined like backup to offsite locations.
Avatar of isdd2000
isdd2000

ASKER

Hi Patrick,

So you don't recommend indexing, checking for corrupt entries or any of the other things Microsoft recommend?
Hello,
Maintenance is a regular task and depends on various factors like the amount of transactions (DMLs) happening at DB end, amount of load on sql server etc. As this is an open end question, here are some pointers that will help you:
1. DB corruption - To avoid this, DBCC CHECKDB needs to be executed every week or fortnight based on the load and the output needs to be checked for any errors
If errors are being thrown, then they should be repaired accordingly (there are MS articles for the same in technet/msdn)
2. Index defrag/rebuild activity - This needs to be done regularly during less load times. Defrag needs to be done when the fragmentation is not too high, where as rebuild should be run when the fragmentation is high
3. Log backups - Log backups need to be run at regular intervals onto disk or tape
4. Statistics - Statistics need to be updated at regular intervals to ensure that the SQL Server is aware of what plan needs to be used and help it to fetch the data faster and easier

You can find more details in the below article:

http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Hope this helps!
Hi again,

I do recommend indexing and checking for corrupt entries, i just dont recommend it to be done in a scheduled task. It is the job for a DBA to have this hands/eyes on if you ask me.

We have scheduled tasks in place for creating backups, moving them to offsite location (where we have other scheduled tasks to import it) so we are 100% redundant and failover proof.
ASKER CERTIFIED SOLUTION
Avatar of Sagir87
Sagir87

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial