Solved

SQL Maintenance Plans Advice

Posted on 2013-06-04
3
365 Views
Last Modified: 2013-06-06
Experts, we have SQL server 2005 and I am by no means an expert. I need advice on what we are doing and what we need to change with regard to our backups, and maintenance. This was all set up with little knowledge, but as I search around, true experts will probably say: "Here's how you really should do it."

Here is what we do based on our recovery policy:

1. We backup transaction logs every 2 hours using maitenance plan (works fine)
2. We backup database (full) every night
3. Recovery Model: Full
4. Reorganize and Rebuild indexes once a week.
5. We used to shrink database very week but no longer (see below)

Code to rebuild/reindex:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Open in new window


I have read that shrinking a database is NEVER what you should do:
http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/

Also, I can never tell if the maintenance plans actually works (reindex) as it says successful.

My questions are basic: based on above, how can we improve what we are doing?

What is the proper sequence for index rebuilds/reorgs?
0
Comment
Question by:whosbetterthanme
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39220271
seems you are using the Ola Hllegran's reindex stored procedure, if there is no changes you guys made to his ps, then you are good,
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 39220276
0
 
LVL 7

Author Comment

by:whosbetterthanme
ID: 39220321
Ah yes - thank you for giving credit as I should have!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Getting the 3 middle digits 4 35
SQL help 5 51
SQL Select - Finding chars in a column 2 56
passing parameter in sql procedure 9 56
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now