Post reply

DBMaintenance Plan

  • October 31, 2006 at 2:20 pm

    #115246

    Hi All!

    We have SQL Sever 2000 SP4 installed.  We are running a maintenace plan to backup the database files and log files m-f and then we test the integrity with include indexes, and fix minor problems. 

    Everywhere I look it states that you need to put the Database into Single user mode. If you do this and then first thing monday morning(seeing it is done sunday morning at 12 am) you need multiple connections to the database for the accounting software would that be an issue.  what is the best way to do this.

    Thanks

    I am still fairly new at SQL Server and I am working away at learning the program

  • October 31, 2006 at 4:11 pm

    #669106

    don't know where you are looking; but you do NOT need to put the database in single user mode to run a backup maintenance plan.

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • October 31, 2006 at 4:26 pm

    #669108

    When you ask it to fix minor problems during the intergrity check it seems like you need to.  Here is the Error message I was getting from SQL server

    Error number 7919.

    Activity: Check Data and Index Linkage

    [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

  • October 31, 2006 at 4:51 pm

    #669112

    guess that makes sense... I would not let it fix regularly then... maybe that's why none of the dbs I inherited have it checked...

    Here's how I get people off quickly...

    alter database "<dbname>"

    set single_user WITH ROLLBACK IMMEDIATE

    GO

    and when it's okay to go back, I do the following:

    alter database "<dbname>"

    set multi_user WITH ROLLBACK IMMEDIATE

    GO

    obviously, connections will have to be remade.

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • November 1, 2006 at 8:19 am

    #669221

    Thanks for you help.  I will ensure try and see how it works.

  • November 1, 2006 at 2:29 pm

    #669342

    Why do you have so much fixing to do ?

  • November 3, 2006 at 3:19 pm

    #669928

    I just modified a plan, asked it to fix minor problems... and it completed in 5:39... no problems, success reported, and no messages anywhere to the contrary.

    I would appreciate it if someone else could shed some light on this one.

    Thanks,

    David

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

Viewing 7 posts - 1 through 6 (of 6 total)

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