Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Maintenance plan jobs failing

    I have a problem with my DB maintenance plans, I have scheduled the usual optimisation, integrity checks and backup of Tlogs and Databases.

    On one instance/db, the only jobs that run are the optimisation, integrity and backup fail instantly. On another instance/db I can backup the tlog, do integ and optimise but the full backup fails. I thought this was a permissions fault so I set the owner to SA, this got me further but I am still having trouble.

    Here is the background:

    SQL 2000 Enterprise edition clustered on a RAID 10 SCSI array with an active/passive cluster.

    The accounts that own and excute the jobs are AD Domain accounts, we have different service and agent accounts. The accounts work, I have even logged into the box using these accounts. I also checked their access to the cluster drives, no probs there.

    I thought it sounded like a permissions problem and changed the job owners to SA but it only helped on one instance, the other DB jobs still fail, previously i had made sure the the agent and service accounts were db owners.

    The event logs are a bit sparse, so I set the job step to output to a file and got what follows at the end of this message

    What stands out to me are three things:

    1. Database state cannot be changed while other users are using the database

    and

    2. Database needs to be in single user mode.

    and

    3. The backup was not performed since data verification errors were found.

    Surely if you create a maintenance plan to do these sorts of jobs, they should put the DB in the state it needs to be in. I used to do these jobs under SQL Server 7 and could even execute backups during the day if they had failed the night before for whatever reason.


    Any ideas would be appreciated

    AJ


    ------------------------------------------------------------------------------------

    Job 'Transaction Log Backup Job for DB Maintenance Plan 'MY Plan'' : Step 1, 'Step 1' : Began Executing 2003-12-22 17:40:29

    output
    ------------------------------------------------------------------------------------(null)
    Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
    Copyright (C) Microsoft Corporation, 1995 - 1998
    (null)
    Logged on to SQL Server 'INSTANCE\SERVER'
    as 'DOMAIN\SQLService' (trusted)
    (null)
    Starting maintenance plan 'MY Plan' on 12/22/2003 5:40:29 PM
    (null)
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'DATABASENAME'
    [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
    [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
    (null)
    [1] Database DATABASENAME: Check Data and Index Linkage...
    (null)
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    (null)
    (null)
    The following errors were found:
    (null)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    (null)
    ** Execution Time: 0 hrs, 0 mins, 1 secs **
    (null)
    (null)
    [2] Database DATABASENAME: Transaction Log Backup...
    (null)
    (null)
    The backup was not performed since data verification errors were found.
    (null)
    (null)
    End of maintenance plan 'MY Plan' on 12/22/2003 5:40:29 PM
    (null)
    SQLMAINT.EXE Process Exit Code: 1 (Failed)
    (null)
    Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    The problem sounds like for your integrity check - if a user is in that database when it is performed, it will fail because it will not be able to transition the database to single user mode.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •