SQL Server Performance

detach temp db not alter

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

  1. avipenina New Member

    maybe you don't know or haven't try it but if you detach your tempdb or for some reason you have problem with the sys.databases table in the master database that is related to your tempdb database you can't start sql service. it say that you have problem with database ID 2 which is the tempdb ID.
  2. satya Moderator

    It seems you aren't ready or unable to understand what has been referred above.
    I would suggest you to talk to MS PSS in this regard.
  3. Adriaan New Member

    Or uninstall SQL Server, and re-install from the ground up.
  4. trainr New Member

    [quote user="avipenina"]how i set my TempDB back to working state without restore the Master database from backup[/quote]
    Restart SQL Server and stop worrying about tempdb. There is absolutely NO reason to try to restore it.
    However, if you have a compelling reason to do so, go ahead and try.
    For what reason do you need to re-attach or restore tempdb?
  5. avipenina New Member

    i will explain my self last time.
    i entered in single user mode to change all LDF file to different disk. when i arrived to change the LDF for the TempDB database instead of do alter to the LDF file i by mistake do detach TempDB command. the command complete successfully and the TempDB database is now detach.
    and from now on after i do that by mistake i can't reattach the TempDB database or restart the SQL service because it say that the TempDB is missing. i hope that you understand now what happen and if you have a way to resolve this issue? (i can say that restore the master database to before the LDF changes works fine and SQL service start. but i'm searching for a way to resolve this issue without restore the master database(all of this is in my test environment sql off curse))
    Thx
  6. Adriaan New Member

    So the SQL Server instance is currently down? Let's hope you know, for each database, where all the files are. And if you've added items to the model system database, I hope you have a backup of that as well.
    You can rebuild the master database - check Books Online for all the details. This should also rebuild tempdb (and model).
    After the rebuild, you have to re-attach the databases.
  7. trainr New Member

    [quote user="avipenina"]i can't reattach the TempDB database or restart the SQL service because it say that the TempDB is missing. i hope that you understand now what happen and if you have a way to resolve this issue?[/quote]Okay, sorry I didn't understand that you couldn't restart the service.
    The best answer then appears to be to rebuild the system databases and either 1) re-attach the user databases afterward, or 2) restore the master and msdb from backup.
    The rebuildm.exe utility will rebuild the system databases. When it completes, SQL Server should start and create a new tempdb.[quote user="avipenina"]i'm searching for a way to resolve this issue without restore the master database[/quote]
    I don't think there is a way to do this without the above steps.
  8. Luis Martin Moderator

    "yes you can.
    start your SQL in single user mode like you want to alter the Tempdbto different location,but instead of alter tempdb,do sp_detach_dbTempdb and you will see that you can detach the tempdb(backup yourmaster DB before)."
    Sure. Also you can delete your production database and don't have any backup.

Share This Page