Post reply

tempdb deleted

  • October 20, 2004 at 6:29 pm

    #162524

    Hi,

    One of my colleague detached tempdb by mistake and couldn't attach it. Sql server stopped and couldn't start it. It gives error and stops on tempdb. I can't rebuild master database otherwise it will rewrite all sysdatabases. I dont have backup of any system databases or userdatabase. I can copy all database files,(msdb and userdatabases) but i will loose master database in that case which i can't afford. Any ideas about it? I tried starting sql server in single user mode but it didn't start.

    I can recover information about msdb, and all user databases by using sp_attach_db with current files on new installation.

    So basically i am not able to start sql server in single user mode.

    I dont have back up of master database.

    I can't rebuild master as it will loose all information from master database.

    Any ideas will be appreciated? Thank you for your help in advance.

  • October 21, 2004 at 7:17 am

    #527452

    Did you try starting the server in minimally configured mode? Check the -f parameter in sqlservr.exe.

    Never happened to me but I thoght that SQL should rebuild by itself tempdb database each time it starts.

  • October 21, 2004 at 8:05 am

    #527468

    I tried start sql server with -f parameter from command prompt but didn't start. I think this is what happened in this case, this is my understanding please correct me if i am wrong. My friend ran the command sp_detach_db tempdb and it ran successfully. so sql server removed entry for tempdb from sysdatabases and sysaltfiles tables. Now when i try to start it, it doesn't find entry for tempdb and it fails.

    Any suggestions will be appreciated.

    Thank you

  • October 21, 2004 at 10:58 am

    #527507

    Rebuild of of master or a reinstall is the only things I can think of.

     

  • October 21, 2004 at 11:39 am

    #527518

    If you were a good DBA and took regular backups you should be able to recover with a restore of your last master dba backup.

  • October 21, 2004 at 11:44 am

    #527519

    The problems is this server is not supported by my team. So we had no ideas about what's going on this server. They contacted us when they got this problem created. So Now what can be done now?

  • October 21, 2004 at 2:15 pm

    #527541

    Deven,

    Try this to at least get your data from the broken Master database. This advice does not carry any warranty and may not work. More over, follow it only on the test server on your own risk. No responsibility for any lost data.

    1. Save your master.mdf and master.ldf somewhere safe. Copy those 2 files under another name like MasterCopy mdf and ldf files

    2. Get to another instance of SQL Server, preferably same edition, version and SP and patch level. Attach your MasterCopy.mdf and ldf to this second instance under another name, say, TestDB. While attaching DO PAY ATTENTION to the Physical Name that should point to MasterCopy.mdf and ldf. By default when you navigate to your MasterCopy.mdf it would put an Original path Master.mdf, you don't really want that.

    3. So now your original Master is attached under the name of TestDB and you can open it. Check sysdatabases in theis TestDB. Import a record for Tempdb from to TestDB..sysdatabases from Master..sysfiles. Same with Sysdevices.

    I went only as far as attaching a copy of Master under another name and I was at least able to open a TestDB database.

     

     

    Yelena

    Regards,Yelena Varsha

  • October 22, 2004 at 2:43 am

    #527607

    EXEC sp_detach_db 'tempdb'

    gives

    Server: Msg 7940, Level 16, State 1, Line 1

    System databases master, model, msdb, and tempdb cannot be detached.

    Unless SQL had trace flag 3608 (from memory, and that's for msdb and model I think) how did you manage it?

    I had a corrupt tempdb once and SQL wouldn't start. I used -f, it started, stop it (DELETE THE tempdb FILES!!!) and start normally

  • October 22, 2004 at 3:35 am

    #527613

    Hi,

    try starting SQL-Server with trace flag 3608, go to table sysaltfiles and look, if there are entries for tempdb.

    If no, create them, if yes, control the path...

    Hope this helps

    karl

    Best regards
    karl

  • October 22, 2004 at 6:52 am

    #527631

    Hi Everybody,

     

    Thank you for all your help and suggestions.

    GBN I am not sure how this happened as when i got to know that i have to fix this server the only information i got from previous person is, they had detached tempdb. I will check with them what exactly they did.

    Yelena and Karl thanx for your suggestions, I will test both the methods and post it wether it was successful or not. But thanx for your suggestions.

  • October 22, 2004 at 7:17 am

    #527640

    I once detached tempdb to see what happens, and how to get things working again.  As far as I can remember, this is what I did...

    1) Start SQL with the -T3608 flag.  Look on the KB if you want to know what T3608 does.

    2) Create a database called tempdb

    3) Use EM or QA to allow updates to be made to the system catalogues

    4) Manually update master..sysdatabases.  Set the dbid value for tempdb to 2.

    5) Use EM or QA to prevent updates to the system catalogue. 

    6) Remove the -T3608 flag and restart SQL.

    The big problem with detaching tempdb is that SQL will prevent you creating a database with a dbid value lower than 3, the value used by model.  However, SQL expects that database 2 will exist and will be tempdb.  Therefore, you have to do a 2-stage process of creating (or attaching) tempdb, then patching sysdatabases to give tempdb its correct dbid.

    Alternatively, you can restore master from a backup that includes tempdb (you do have backups...?)

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • October 22, 2004 at 10:08 am

    #527684

    The following steps explain how you can create a new tempdb database. With this workaround, you are able to successfully start the SQL Server service.

    1. If they exist, rename the current Tempdb.mdf and Templog.ldf files. If the files do not exist, that is one possible reason for the suspect status, the files are missing.
    2. Start SQL Server from a command prompt by using the following command:

      sqlservr -c -f -T3608 -T4022

      For SQL Server 2000 named instances you have to add the additional -s parameter.

      On a SQL Server 7.0 cluster, you must first run this statement:

      set _CLUSTER_NETWORK_NAME=YourSQLVirtualName

      This will allow SQL Server to start from a command prompt.

      Note Make sure that the command prompt window remains open after SQL Server starts. Closing the command prompt window terminates the SQL Server process.

    3. Connect to the server by using Query Analyzer, and then use the following stored procedure to reset the status of the tempdb database. 
      exec master..sp_resetstatus Tempdb

    4. Shut down the server by pressing CTRL+C in the command prompt window.
    5. Restart the SQL Server service. This creates a new set of tempdb database files, and recovers the tempdb database.

     

    For details, please check MS KB 288809.

     

  • March 23, 2011 at 6:59 am

    #1302433

    sqlservr.exe -f worked for me.

  • April 28, 2011 at 6:01 am

    #1317183

    Eventhough this thread is old. Iam adding my comments to this thread thought that it can be beneficial for some others.Recently I have encountered the situtation where I need to start the SQL instance in 3608 mode to change the path of tempdb.But I did not get the detailed steps on that .

    Here I have attached the detailed step to start SQL server in 3608 flag.


    UmaShankar

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply