question

melwin avatar image
melwin asked

SQL 2008 R2 Maintenace Tasks

Hi, Which are the maintenace tasks I need to run everyday for a healthy database backup which is running in full recovery model. Also the best order for the tasks. Please reply as soon as possible. Thanks in advance! Melwin Lawrance,
sql-server-2008maintenance-plansmaintenance
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
ThomasRushton avatar image
ThomasRushton answered
Have a read of Brad McGehee's Sure Guide to SQL Maintenance Plans - http://www.red-gate.com/community/books/#sqldba. Free PDF download.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
The quick answer? "It depends." The full answer - read the book, evaluate what you read bearing in mind your databases, servers, storage, infrastructure, business requirements, working hours, recovery point objectives, recovery time objectives...
2 Likes 2 ·
melwin avatar image melwin commented ·
Thanks for the response. I will read the pdf, but i need a quick answer to start...
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Right. "Quick Answer". 1. Full backup - regularly. 2. (depending on Recovery Time objective) Differential backup - more frequently than full backup 3. (depending on Recovery Time & Recovery Point objectives) Transaction Log backups - more frequently 4. DBCC CheckDB 5. Index rebuilds (please use something intelligent than the standard script) Backups are useless unless you test them, so build some of that into your schedule. Smarter index restores - see scripts by Michelle Ufford ( http://www.sqlfool.com) and Ola Hallengren ( http://ola.hallengren.com) for more. Note that none of these things will tell you how often to run the jobs. Note also that performance will be affected while any of these jobs are running. House prices can go up as well as down, you may lose your home if you fail to keep up with repayments, and the dish ran away with the spoon.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - for formatting the Get Out Of Jail Free clause
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.