Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    11

    Unanswered: Load Tempdb Error! How Can I set exclusive use of database

    load database tempdb from "f:\tempdb.bak"

    Server Message: Number 3101, Severity 16
    Line 1:
    Database in use. A user with System Administrator (SA) role must have exclusive use of database to run load.

    How can I do?

    Thanks you a lot !

  2. #2
    Join Date
    Sep 2003
    Posts
    17
    Hi,

    In general when you load a database, no one is allowed to be connected to this database.

    sp_who
    go
    -- get the spids connected to you db
    kill spid
    go

    Kill all connections.

    Why do you want to load tempdb?

    Regards,
    Ulrike

  3. #3
    Join Date
    Feb 2009
    Posts
    19
    if a rep agent is running on that database
    kill command will it work ?
    Bcoz in my case i have to explicitly state --- sp_stop_rep_agent 'dbname'
    killing that spid doen't work

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Generally there is no need to load the tempdb. It gets rebuild every time your dataserver starts, without any content.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Jan 2009
    Posts
    14
    Hey frnds,

    backup of tempdb can be taken.
    But you cannot restore this on any temporary database.
    you can restore the backup of temporary database on to user database.

  6. #6
    Join Date
    Feb 2009
    Location
    Prague
    Posts
    17
    Of course, tempdb is used for worktables, every system procedure requires free space in tempdb when run. A process may be using tempdb and it might not be visible in sp_who output. I'm afraid you cannot prevent tempdb from being used (and have system properly working). I agree that it is nonsense to load tempdb dump into tempdb.

  7. #7
    Join Date
    Jun 2009
    Posts
    25
    Hi,

    I think there willnot be any need to dump a tempdb and load a tempdb to a tempdb.

    If found database in use while loading a database then someone may be using the current database what u r loading. Also for loading a database the user should have the sa role.

    Thanks and Regards
    Last edited by erovij; 07-29-09 at 07:25.

Posting Permissions

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