Link to home
Start Free TrialLog in
Avatar of whosbetterthanme
whosbetterthanmeFlag for United States of America

asked on

SQL Maintenance Plans Advice

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?
Avatar of Aneesh
Aneesh
Flag of Canada image

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,
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

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
Avatar of whosbetterthanme

ASKER

Ah yes - thank you for giving credit as I should have!