Solved

SQL Maintenance Plans Advice

Posted on 2013-06-04
3
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
passing parameter in sql procedure 9 68
How to disable/enable multiple sql jobs in efficient way 11 129
configure service broker on all databases 2 94
SQL query with cast 38 61
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

730 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