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.
Microsoft SQL ServerIT AdministrationMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Sagir87

8/22/2022 - Mon
Patrick Bogers

Hi,

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

Hi Patrick,

So you don't recommend indexing, checking for corrupt entries or any of the other things Microsoft recommend?
geek_vj

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!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Patrick Bogers

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
Sagir87

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question