Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    Angry Unanswered: SQL Maint. Plan Help

    I have setup maintenance plan to run nightly.

    (1) Maint plan is setup to backup the databases that users actually use to update company information (2) diffrent databases.
    (2) maint plan backs up the master database this is setup to run by itself.
    (3) maint plan is setup to backup the model - msdb databases together.

    I setup the email notification option to inform me if my backups were successfull. below is an example that I'm recieving everymorning on every database. Something about the single user mode is causing the problem. can somone explain this to? Maybe give me a direction to follow to correct this problem. Below is an example error message that I'm recieving.

    I can backup any database by right clicking selecting all tasks then backup database and I get a complete backup to problem. Why is my Maint. Plan not going through. the rebuilding of indexs is successful.

    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'My Server name' as 'My_Domain_Name\My_SQL_Admin_Account (trusted)
    Starting maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:00 PM
    [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 'Leo'
    [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
    [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
    [1] Database Leo: Check Data and Index Linkage...
    [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.

    The following errors were found:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [2] Database SlcBom: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    Deleting old text reports... 0 file(s) deleted.

    End of maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:01 PM
    SQLMAINT.EXE Process Exit Code: 1 (Failed)

    Emailing report to operator 'Admin, SQL'

    Thnaks for any help

    SQL Rookie

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    OK You built your DB maintainance plan (probably using the wizard) in such a way that you included a job that invokes xp_sqlmaint to do integrity checks, or some other operation, that ultimately invokes a command (like ALTER DATABASE) that requires the SQL Agent connection that invokes the job to be the only live connection to the database.

    Look in EM->Server->Management->SQL Server Agent->Jobs

    Is there a job called something like "Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan1'"? If so disable it.

    Otherwise do right-click->properties->Steps->Edit on each Job in the SQL Agent job tree, and copy and paste the SQL in each step into QA and try executing it until you find the piece that won't run with more than user connected. Edit or disable that step or Job.

    Hope this helps

    HH

Posting Permissions

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