Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2001
    Posts
    24

    Unanswered: Lock db exclusively

    I have a .Net process (console application) that loads data from a transactional db to a reporting db. This process is failing occasionally due to users remain connected to the reporting db.
    I want to lock the reporting db exclusively before I start my process. If there are any users connected to it, I should be able to kill their processes. is this possible? If yes, how?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on your version of SQL Server, it is probably possible.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2001
    Posts
    24
    possible with SQL Server 2008 ? If so, how?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can put it in single-user mode, but I've been bitten by this many times. Another process jumps in and grabs the lone connection, and then I can't even get in to change the setting again.
    Better might be to put it in admin-only mode, and make sure that only the processes you want to be accessing it are sysadmin logins.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2001
    Posts
    24
    Does logging in as admin require using enterprise manager on the server?
    I am running a process on a different client machine

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    No, it doesn't. Use EM or SSMS on your local workstation.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Setting a database to RESTRICTED_USER will still allow members of the db_owner role to connect to the database. This has bitten me once, as well.

  8. #8
    Join Date
    Aug 2001
    Posts
    24
    can it be done via command line? If yes, I will use the commands in the .net program

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can open a connection to the server (SqlConnection), and issue the command with a SqlCommand object.

Posting Permissions

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