SQL Server Performance

detach temp db not alter

Discussion in 'SQL Server 2005 General DBA Questions' started by avipenina, Aug 25, 2007.

Page 1 of 2

  1. avipenina New Member

    HI,
    i use my Test sql environment and i start sql service with the /c /m /T3608 for alter tempdb but i detach it and i can't attach it back.
    when i try to attach it back i get this error "Database 'tempdb' already exists." when it's not exist in select * from sysdatabases.
    and when i try to start the sql serive without the /c /m /T3608 it fail.
    how i can reattach my tempdb?
    Thx
    avipenina, Aug 25, 2007
    #2

  2. Luis Martin Moderator

    From MS:
    Moving the tempdb database

    You can move tempdb files by using the ALTER DATABASE statement.
    1.Determine the logical file names for the tempdb database by using sp_helpfile as follows:
    use tempdb
    go
    sp_helpfile
    go
    The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
    2.Use the ALTER DATABASE statement, specifying the logicalfile name as follows:
    use master
    go
    Alter database tempdb modify file (name = tempdev, filename = 'E:Sqldata empdb.mdf')
    go
    Alter database tempdb modify file (name = templog, filename = 'E:Sqldata emplog.ldf')
    go
    You should receive the following messages that confirm the change:
    Message 1
    File 'tempdev' modified in sysaltfiles.Delete old file after restarting SQL Server.
    Message 2
    File 'templog' modified in sysaltfiles.Delete old file after restarting SQL Server.
    3.Using sp_helpfile in tempdb will not confirm these changes until you restart SQLServer.
    4.Stop and then restart SQL Server.
    Luis Martin, Aug 25, 2007
    #3

  3. avipenina New Member

    the problem is that i already detach the tempdb DB and i can't use the alter database because the tempdb is not listed in the
    master..sysdatabases
    avipenina, Aug 25, 2007
    #4

  4. satya Moderator

    Which is by default due to the configuration, where you will not be able to use detach/attach method for system databases.
    For more information about how to move system databases in SQL Server 2005, see the "Moving System Databases" topic in SQL Server Books Online. To view this topic, visit the following Microsoft Developer Network (MSDN) Web site:
    http://msdn2.microsoft.com/en-us/library/ms345408.aspx and one of the KBA refers:
    Moving the tempdb database

    You can move tempdb files by using the ALTER DATABASE statement.
    1.Determine the logical file names for the tempdb database by using sp_helpfile as follows:use tempdbgosp_helpfilegoThe logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
    2.Use the ALTER DATABASE statement, specifying the logical file name as follows:use mastergoAlter database tempdb modify file (name = tempdev, filename = 'E:Sqldata empdb.mdf')goAlter database tempdb modify file (name = templog, filename = 'E:Sqldata emplog.ldf')goYou should receive the following messages that confirm the change:
    Message 1
    File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
    Message 2
    File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
    3.Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
    4.Stop and then restart SQL Server.
    satya, Aug 27, 2007
    #5

  5. avipenina New Member

    what i need to know is how after i detach the tempdb i restore it back.
    not how to move the tempdb, this i know how. i just test what happen when i detach the tempdb and i can't set it back.
    avipenina, Aug 27, 2007
    #6

  6. Luis Martin Moderator

    You can't detach sysdatabases, only move as we wrote before.
    Luis Martin, Aug 27, 2007
    #7

  7. avipenina New Member

    yes you can.
    start your SQL in single user mode like you want to alter the Tempdb to different location,but instead of alter tempdb,do sp_detach_db Tempdb and you will see that you can detach the tempdb(backup your master DB before).
    now i did this on my test environment and i can't reattach the tempdb backup, i don't want restore it from master backup which it's works,instead i want to try reattach it in any way.
    does any one how to reattach the tempdb after you detach instead of alter it?
    avipenina, Aug 27, 2007
    #8

  8. ghemant Moderator

    ghemant, Aug 27, 2007
    #9

  9. satya Moderator

    You will not be able to re-attach it as we are explaining, this is because the SQL binaries are in use, instead you can only reinstall SQL Server again to recreate the TEMPDB in this case. That is the reason the links give you explains to use only ALTER DATABASE.
    Hope this helps.
    satya, Aug 28, 2007
    #10

  10. avipenina New Member

    after investigating this issue because it's interesting me i try this on a test environment and here is the results with open questions that i have.
    after starting sql in single user mode i'm able to do sp_detach_db Temp db. why i'm able to do this if i can't reattach it? all my effort to reconnect this Temp db where unsuccessful. satya says "You will not be able to re-attach it as we are explaining, this is because the SQL binaries are in use".what if i tell you that you can restore the Temp DB but not in TSql command, but you need to restore your master DB before the Temp DB detach and everything is back to normal. now my question is does someone knows how to reattach detached Temp db by mistake. lets say that i don't have a master DB backup.
    Thx
    avipenina, Aug 28, 2007
    #11

  11. alzdba Member

    - SQL2005 isn't that easy to mess with system objects [H].
    - Backups (at least of the system databases master and msdb (and model if you modified it)) are a MUST.
    - Keep in mind there is no need to recouver the data of tempdb, because it gets cleared with every start of the instance. So don't create objects in tempdb !
    alzdba, Aug 28, 2007
    #12

  12. Adriaan New Member

    If you deliberately detach a system database, you're begging for trouble. It's not something you would be able to do by pure accident
    Check BOL for "Rebuild Master utility", selecting the "How to" item.
    Adriaan, Aug 28, 2007
    #13

  13. thomas New Member

    [quote user="Adriaan"]
    If you deliberately detach a system database, you're begging for trouble. It's not something you would be able to do by pure accident
    Check BOL for "Rebuild Master utility", selecting the "How to" item.
    [/quote]
    Very well said. Just don't do it.. even if you can!
    thomas, Aug 28, 2007
    #14

  14. satya Moderator

    True, as said by Alzdba that is how they are tightening the screws from SQL 2005 onwards, though its possible to play with system tables only by 1 way [:)].
    satya, Aug 28, 2007
    #15

  15. satya Moderator

    Thats a good question, is this on SQL 2000 or 2005?
    satya, Aug 28, 2007
    #16

  16. trainr New Member

    [quote user="avipenina"]why i'm able to do this if i can't reattach it?[/quote]
    No idea, but it doesn't matter anyway, because every time you start SQL Server tempdb is recreated. You always lose tempdb whenever you shut down your sever. And you get a new one when it starts. So, there's no point trying to re-attach it or restore it.[quote user="avipenina"]does someone knows how to reattach detached Temp db by mistake.[/quote]There's no reason to even consider it.
    trainr, Sep 6, 2007
    #17

  17. satya Moderator

    The whole concept being not allowing TEMPDB to attach is due to its entity of being a SYSTEM database that is required to startup the SQL Server services, due to the nature you might have done detach and based on the restrictions the ATTACH method for system database is not allowed in SQL Server product.
    Hope this is clear.
    satya, Sep 7, 2007
    #18

  18. avipenina New Member

    i know all of that. my simple question is: after i mistake do detach TempDB instead of alter when i'm in single user mode, how i set my TempDB back to working state without restore the Master database from backup(i tried to restore the master and it's working fine the TempDB is backup on line)
    avipenina, Sep 7, 2007
    #19

  19. Adriaan New Member

    As has been pointed out already, you have to let SQL Server manage tempdb by itself.
    Shut down the instance of SQL Server, and bring it back up again - that's the only way to have tempdb in working order.
    tempdb contains only volatile data and objects, so it should not contain anything that would ever need to be backed up.
    Adriaan, Sep 7, 2007
    #20
Page 1 of 2

Share This Page