whosbetterthanme
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:
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?
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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah yes - thank you for giving credit as I should have!