Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Posts
    3

    Unanswered: USER database won't backup.

    I have a mainenance plan running on all my user databases. One database in particular is giving me problems. In the mainenance plan history it give me this:

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

    The Activity is: Check Data and Index Linkage


    Anyone got any suggestions?

    Thanks,
    K'Tigre

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Maybe this is a stupid suggestion, but have you tried setting it in single user mode?

  3. #3
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    I had the same problem a couple of months ago, It occured twice and never again since then, I suppose that for that task, sql server tries to switch db into single user mode itself and in those cases some connections remained in db and sql server couldn't do so, but take it as only my deduction. martin

  4. #4
    Join Date
    Oct 2002
    Posts
    3
    Originally posted by Coolberg
    Maybe this is a stupid suggestion, but have you tried setting it in single user mode?
    Actually I've tried, but it won't let me. Something's preventing me from doing it. Unfortunately I really don't know SQL Server, so i don't know how to track down why.

    K'Tigre

  5. #5
    Join Date
    Jul 2002
    Posts
    229
    Yes, there must be exactly one connection (=you) running when you set it in single mode. Note that having the database selected (blue-coloured) in ENterprise Mgr is enough to establish a connection that disturbs when someone else tries to set it in single mode.

    You can check under Current Activities in Enterprise Mgr who's connecting.

  6. #6
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Originally posted by Coolberg
    Yes, there must be exactly one connection (=you) running when you set it in single mode. Note that having the database selected (blue-coloured) in ENterprise Mgr is enough to establish a connection that disturbs when someone else tries to set it in single mode.

    You can check under Current Activities in Enterprise Mgr who's connecting.
    what is the syntax for setting single user mode in query analyzer?

  7. #7
    Join Date
    Oct 2002
    Posts
    369
    Originally posted by clinel
    Q1 what is the syntax for setting single user mode in query analyzer?
    A1 You may use Alter Database or sp_DBOption (you have to take some precautions, and also set just prior to the maintnance to make sure some user connection isn't active when you change to single user):

    Alter DataBase DB
    Set SINGLE_USER With RollBack Immediate

    USE master
    EXEC sp_dboption DB, 'single user ', 'TRUE'

  8. #8
    Join Date
    Jul 2002
    Location
    Texas
    Posts
    2

    Smile Re: USER database won't backup.

    Not sure if you fixed this yet but if you take off the repair (uncheck) in the integrity job that you are running - it wont error out but you need to run DBCC CHECKALLOCATION (see SQL books for syntax) to correct any integrity or index coruption problems. This checkmark causes the database to stay in readonly mode sometimes when if doesn't finish the repair.

    Hope this helps.

    Mary

    Originally posted by ktigre
    I have a mainenance plan running on all my user databases. One database in particular is giving me problems. In the mainenance plan history it give me this:

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

    The Activity is: Check Data and Index Linkage


    Anyone got any suggestions?

    Thanks,
    K'Tigre

Posting Permissions

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