Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server 2005 Installation – Maintenance Plan Using Wizard

By Exforsys | on November 29, 2005 |
SQL Server 2005

SQL Server 2005 Installation – Maintenance Plan Using Wizard

Maintenance plans can be created using the Maintenance Plan Wizard or using the design surface. The Wizard is useful if the DBA wants to create a basic maintenance plan. If he intends to create enhanced work flow then, it is advisable to use the design surface. Maintenance Plans are displayed only to users connected using the Windows Authentication.

 

Invoking The Maintenance Plan Wizard from Add/Remove Programs.

One method of accessing the Maintenance Plan Wizard is by navigating to the Add/Remove programs panel of the Operating system. Each option of SQL Server is listed in the panel.

1. In Control Panel, double-click Add or Remove Programs.

2. Under Currently installed programs, click the instance of SQL Server to configure, and then click Change.

3. On the Change or Remove Instance page, click Change Installed Components.

4. On the Feature Selection page, select or clear features.

5. Click Next, and then follow the instructions on subsequent wizard pages.

Invoking The Maintenance Plan Wizard from SQL Server Management Studio.

The Maintenance Plan Wizard helps set up the core maintenance tasks for optimum performance of the SQL Server components.

To start the Maintenance Plan Wizard

1. Expand the server.

2. Expand the Management folder.

3. Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.

The first step is to give a name to the Maintenance plan along with a description. Enter the name, description, the server details and authentication mode and click Next.

The next step is to specify the areas where the DBA wants to set up Maintenance options.

The Execute SQL Server Agent job cannot be selected unless jobs have been assigned.

The Next screen prompts the Administrator select the order in which he wants the maintenance tasks performed. He can move the items up and down the list until he is satisfied with the order using the Move up.. and move down… buttons.

The next task is to specify the details for each task. The DBA is taken through Wizard screens for each of the tasks he has selected. The first is the Shrink Database task as per the list above. The DBA now specifies the databases in the window that pops up(illustrated below) when he clicks on the Databases combo box. After specifying the databases he returns to the Wizard screen and specifies the event at which the Shrinking should happen and the amount of free space that should remain after the shrink. He has also to specify whether the space should be retained or returned to the operating system.

The Next task is the definition of the Database check integrity task. By default this is the first task. The user has to select the databases and specify whether indexes have to be included or not. The window that pops up is similar to the one above.

The Next task is to enter the specifications for the Reorganize Index task. Select the tables from the popup window and specify the tables and views required. The Administrator has to also specify whether he wants to compact large objects or not.

Rebuilding the Index involves selection of the databases, objects and setting the space options. The DBA can also set Advanced options such as Pad Index, Sort results to tempdb, Ignore duplicate keys and Keep index online while reindexing.

The Update statistics task prompts the user to select the database, objects and the update options.

The Cleanup job specifies what needs to be backed up before cleaning.

The Next task is the definition of Back up options. The first is for Full task. There are a number of options which the user has to specify.

The next backup options are for differential backup. The user has to specify which databases have to be backed up, where it has to be backed up and so on…The user can also set up the option for a back up verification check by checking the check box provided for the purpose.

The Next options required to be set are the Transaction log backup options. Again the user has to specify what to backup, where to backup and so on.. He can also specify a backup integrity verification check.

The Next screen prompts for Plan properties. The user has to specify whether he wants to retain default properties or he wants to change them. If he needs to change them, he has to click on the Change button and navigate through the screens setting the options he is prompted to give as illustrated in the screen shots below.

If the DBA has not unblocked access to dbo.sp_get_sqlagent_properties by turning on the Ágent XPs for the server, he will get an error message prompting him to turn on the Agent XPs by using sp_configure.

Keeping in mind the security requirements, SQL server 2005 selectively installs and activates key features and services. The intention is to reduce the area of exposure of the system. The default configuration has many of the features turned off. The System Administrator can change these features later using the Microsoft SQL Server Surface Area Configuration tool. This tool helps the Administrator enable or disable features, services and network protocols for remote connections. We shall be touching upon how to configure internal settings a little later in this lesson. For the present the DBA needs to understand that an error will be thrown up if the Agent XPs have not been enabled. To enable the Server Agent XP double click on the item in the object explorer and start the service from the SQL Server Configuration manager.

If the SQL Server Agent is enabled, the Wizard will go through the following screens to complete the task of creating the maintenance plan.

« « Installing a Second Copy of SQL Server 2005
SQL Server 2005 Installation – Maintenance Plan Without Using Wizard » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Using XML in SQL Server 2005

    October 26, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment
  • SQL server 2005 Editions

    November 23, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 1

    December 15, 2005 - 0 Comment
  • XML Data Types in SQL Server 2005

    October 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment
  • SQL Server 2005 Installation

    November 23, 2005 - 0 Comment
  • Getting started with SQL Server 2005

    August 9, 2005 - 0 Comment
  • Using SQL Sever Management Studio – Part 2

    December 15, 2005 - 0 Comment
  • Native HTTP Support in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT