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.
isdd2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick BogersDatacenter platform engineer LindowsCommented:
Hi,

SQL server doesnt require much maintenance, tasks to schedule are more user defined like backup to offsite locations.
0
isdd2000Author Commented:
Hi Patrick,

So you don't recommend indexing, checking for corrupt entries or any of the other things Microsoft recommend?
0
geek_vjCommented:
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!
0
Patrick BogersDatacenter platform engineer LindowsCommented:
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.
0
Sagir87Commented:
Hi,

Please go through this link how to configure maintenance plan:

http://technet.microsoft.com/en-us/library/ms191002.aspx

Also check below suggestions of Best Practice Strategy of backing up your databases:

Best Practices Strategy:

Full Backup: Daily
Differential backup: Every 1 hour
Transaction log backup: Every 5 or 10 mins

If you backup as mentioned above you will have transaction log backup size smaller.

As MS recommends perform backup of your system databases daily: master model and msdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.