Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115

    Unanswered: Database Marked as Single User Pls Help

    Hi All,
    Sql Server 7

    I have database called ecatalog

    i have a scheduled job which shrinks the database every day once at 12 am

    today the job got failed

    In the view Job history its showing the below contents
    -------------------------------------------------------
    Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error
    924) DBCC execution completed. If DBCC printed error messages, contact your system
    administrator. [SQLSTATE 01000] (Message 2528). The step failed.


    And in Application Log of event viewer its showing the below contents
    ------------------------------------------------------------------------------
    The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The
    local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database
    - ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed.
    The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..


    I ran the job manually it worked fine

    but now when i go and see the database ecatalog in my Enterprise Manager
    it is showing ecatalog(Single User)
    What is the meaning of this, will this make any problem to my database

    Please help me in this.

    Waiting for Reply

    Adil

  2. #2
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    It means only one connection can be allowed in this db. use sp_dboption 'ecatalog', 'single', 'false' to turn it off.

  3. #3
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    Hi Thanks for reply,

    I wanted to know that does this happens on its own or has some one has done it.

    Thanks waiting for reply
    Adil

  4. #4
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    No it won't do this on its own. check all jobs to see if any sp_dboption command would have done that. Also check sql error logs and look for a series of Closing file, Starting up database and Opening file. that may represent an event to put the db in single user mode.

    Note some operatins do require single user mode, such as using sp_rename to change a db name.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that many of the maintenance jobs will put the database into SINGLE USER mode because it is required for some kinds of maintenance. Those jobs normally take the database back out of SINGLE USER automagically when they complete.

    -PatP

Posting Permissions

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