Post reply

Need help with database maintenance

  • February 27, 2017 at 10:33 am

    #319145

    First, I am not a DBA, I'm a programmer, so I don't have any training in SQL Server administration.  In the past, most of my work has been done on Server-based databases which were maintained by our system administrators.  Lately, however, I have been doing a lot of work on a local SQL Server installation so I am the only one maintaining it.  I had presumed that there would be some sort of default maintenance plan to handle backups and such but recently discovered that that is not the case.  So, I decided to create a maintenance plan.  I saw that there is a "wizard" for that so I figured that was my best bet.

    I walked through the various steps in the wizard, created schedules for backups of both databases and transaction logs, integrity checks, etc.  I set various options and once I had set everything up it presented me with a list of what it was going to do -- the list looked good so I selected "Finish" and we were off to the races.  Unfortunately, it hit an error in creating the schedules.  The error was something on the order of the index value is invalid (which I interpreted to mean that it was out of range).  After reading the logged messages which told me nothing about how to fix the problem, I clicked on the "Close" button (which, as I recall, was the only choice I was presented with).  To my horror, it did not take me back to the setup to try to fix whatever problem existed, it just exited and threw all of my changes away.

    What should I have done?  Was there a way of saving my changes before attempting to create the maintenance plan so that when the creation failed I had something to go  back and edit?  I don't want to go through all of that work again just to have another error wipe out all of my work.

    Thanks for any help you can offer.

  • February 27, 2017 at 10:59 am

    #1930516

    lnoland - Monday, February 27, 2017 10:33 AM

    First, I am not a DBA, I'm a programmer, so I don't have any training in SQL Server administration.  In the past, most of my work has been done on Server-based databases which were maintained by our system administrators.  Lately, however, I have been doing a lot of work on a local SQL Server installation so I am the only one maintaining it.  I had presumed that there would be some sort of default maintenance plan to handle backups and such but recently discovered that that is not the case.  So, I decided to create a maintenance plan.  I saw that there is a "wizard" for that so I figured that was my best bet.

    I walked through the various steps in the wizard, created schedules for backups of both databases and transaction logs, integrity checks, etc.  I set various options and once I had set everything up it presented me with a list of what it was going to do -- the list looked good so I selected "Finish" and we were off to the races.  Unfortunately, it hit an error in creating the schedules.  The error was something on the order of the index value is invalid (which I interpreted to mean that it was out of range).  After reading the logged messages which told me nothing about how to fix the problem, I clicked on the "Close" button (which, as I recall, was the only choice I was presented with).  To my horror, it did not take me back to the setup to try to fix whatever problem existed, it just exited and threw all of my changes away.

    What should I have done?  Was there a way of saving my changes before attempting to create the maintenance plan so that when the creation failed I had something to go  back and edit?  I don't want to go through all of that work again just to have another error wipe out all of my work.

    Thanks for any help you can offer.

    I would recommend that you stay away of Maintenance plans and check the solution by Ola Hallengren https://ola.hallengren.com/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 27, 2017 at 1:03 pm

    #1930530

    Luis Cazares - Monday, February 27, 2017 10:59 AM

    lnoland - Monday, February 27, 2017 10:33 AM

    First, I am not a DBA, I'm a programmer, so I don't have any training in SQL Server administration.  In the past, most of my work has been done on Server-based databases which were maintained by our system administrators.  Lately, however, I have been doing a lot of work on a local SQL Server installation so I am the only one maintaining it.  I had presumed that there would be some sort of default maintenance plan to handle backups and such but recently discovered that that is not the case.  So, I decided to create a maintenance plan.  I saw that there is a "wizard" for that so I figured that was my best bet.

    I walked through the various steps in the wizard, created schedules for backups of both databases and transaction logs, integrity checks, etc.  I set various options and once I had set everything up it presented me with a list of what it was going to do -- the list looked good so I selected "Finish" and we were off to the races.  Unfortunately, it hit an error in creating the schedules.  The error was something on the order of the index value is invalid (which I interpreted to mean that it was out of range).  After reading the logged messages which told me nothing about how to fix the problem, I clicked on the "Close" button (which, as I recall, was the only choice I was presented with).  To my horror, it did not take me back to the setup to try to fix whatever problem existed, it just exited and threw all of my changes away.

    What should I have done?  Was there a way of saving my changes before attempting to create the maintenance plan so that when the creation failed I had something to go  back and edit?  I don't want to go through all of that work again just to have another error wipe out all of my work.

    Thanks for any help you can offer.

    I would recommend that you stay away of Maintenance plans and check the solution by Ola Hallengren https://ola.hallengren.com/

    Thank you!  That looks incredibly useful.  I am confused about one thing, however.  The instructions say that by running the script "the sqlcmd commands and the output files will be configured for you."  But, there are a number of variables which have to be set to control what happens.  Am I supposed to set these things in the script itself before running it, or am I supposed to modify stored procedures or something after the script has run?

  • February 27, 2017 at 2:46 pm

    #1930562

    lnoland - Monday, February 27, 2017 1:03 PM

    Luis Cazares - Monday, February 27, 2017 10:59 AM

    lnoland - Monday, February 27, 2017 10:33 AM

    First, I am not a DBA, I'm a programmer, so I don't have any training in SQL Server administration.  In the past, most of my work has been done on Server-based databases which were maintained by our system administrators.  Lately, however, I have been doing a lot of work on a local SQL Server installation so I am the only one maintaining it.  I had presumed that there would be some sort of default maintenance plan to handle backups and such but recently discovered that that is not the case.  So, I decided to create a maintenance plan.  I saw that there is a "wizard" for that so I figured that was my best bet.

    I walked through the various steps in the wizard, created schedules for backups of both databases and transaction logs, integrity checks, etc.  I set various options and once I had set everything up it presented me with a list of what it was going to do -- the list looked good so I selected "Finish" and we were off to the races.  Unfortunately, it hit an error in creating the schedules.  The error was something on the order of the index value is invalid (which I interpreted to mean that it was out of range).  After reading the logged messages which told me nothing about how to fix the problem, I clicked on the "Close" button (which, as I recall, was the only choice I was presented with).  To my horror, it did not take me back to the setup to try to fix whatever problem existed, it just exited and threw all of my changes away.

    What should I have done?  Was there a way of saving my changes before attempting to create the maintenance plan so that when the creation failed I had something to go  back and edit?  I don't want to go through all of that work again just to have another error wipe out all of my work.

    Thanks for any help you can offer.

    I would recommend that you stay away of Maintenance plans and check the solution by Ola Hallengren https://ola.hallengren.com/

    Thank you!  That looks incredibly useful.  I am confused about one thing, however.  The instructions say that by running the script "the sqlcmd commands and the output files will be configured for you."  But, there are a number of variables which have to be set to control what happens.  Am I supposed to set these things in the script itself before running it, or am I supposed to modify stored procedures or something after the script has run?

    +100 Luis
    You can twist and tweak Ola's scripts in almost any way, you may want to use the Log To Table option rather than clog the file system with text files if you are running full recovery with frequent log backups 😉
    😎
    The documentation and the examples on Ola's site are excellent and trust me, if you have any questions he will help you quickly and accurately.

    Setting the Maintenance solutions up is a two minute thing at the most.

  • February 27, 2017 at 6:36 pm

    #1930596

    Thanks.  I found the info I needed under the FAQ, and I think I am in good shape now.  I am going to need to learn a bit more at some point in the future -- I started off by specifying the wrong name for the backup directory so I got an error when I tried to do a backup.  I tried to delete the jobs the script had set up so that I could run the script again but couldn't figure out how to do it.  Rather than spend time on Google looking for the answer, I took the easy way out -- I renamed the directory.  Some day, however, I really should learn how to manage things better.

    Thanks again to both of you for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply