Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Maintenance Plans Advice

Posted on 2013-06-04
3
Medium Priority
?
382 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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

609 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