SQL Server Performance

When temporary database is created

Discussion in 'General Developer Questions' started by sqldev, Nov 27, 2005.

  1. sqldev New Member

    Hi,

    I would like to know, during what occasions tempdb database is re-created or when the objects in them will be dropped out. In books online, it is mentioned that tempdb is re-created duirng sql server startup. It is fine.

    We found that objects in tempdb disappeared before we stoped and restarted the sql server service. Hence, I would like to know whether it will be cleared out at some other occasion as well, please help, thank you.

    Regards,
    Deva
    sqldev, Nov 27, 2005
    #2

  2. ghemant Moderator

    Hi,<br />AFAIK it only refresh / re-createed only when you stops / restart sql server services .<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
    ghemant, Nov 27, 2005
    #2

  3. johnson_ef Member

    hi Deva,

    tempdb is created while server start up.

    But the temporary objects are created as per the user or application request at runtime.

    This wil be elimiated soon after the session is over, it won't wait till you restart the SQL Service restarts.

    -Johnson
    johnson_ef, Nov 27, 2005
    #2

  4. satya Moderator

    satya, Nov 28, 2005
    #2

  5. johnson_ef Member

    I found something strange or good,

    I have changed my tempdb into 4GB (coz, it was my highest size of my tempdb in last siz months) and I tried to restart the services after changing the size manualy and it was shown as 4GB only instead of its default 8 MB after the service restarted.

    How come it happened? In all documentation it says, it will go to its default 8MB when you restart the SQL Server Services.

    -Johnson
    johnson_ef, Nov 28, 2005
    #2

  6. ghemant Moderator

    Hi,<br />is their something in SQL ERROR LOG or in Windows Event Viewer ? <br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
    ghemant, Nov 28, 2005
    #2

  7. sqldev New Member

    Hi,

    Thank you all of you, even I am able to access the temporary tables only from the session where we create it and not from any other sessions.

    I don't have answer for your questions johnson, sorry.

    Regards,
    Deva
    sqldev, Nov 28, 2005
    #2

  8. johnson_ef Member

    Hemant,

    No log related to this in the SQL Server Logs.
    Clearing tempdb database.
    Starting up database 'tempdb'.

    These are the messages related to tempdb.

    Deva: You can only access the temporary tables only from the session where you have created that.
    You can not refer those tables from any other sessions.

    -Johnson
    johnson_ef, Nov 29, 2005
    #2

  9. ghemant Moderator

    Hi,
    but it should be back to its initial size , its strange !


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
    ghemant, Nov 29, 2005
    #2

  10. johnson_ef Member

    Yes, its strange,

    I have tried it in two servers, one is my test server, and I got a chance to do it in my Production too, We had to shutdown our production server(Active/Passive Cluster in Win2k Advanced Server with Wind2k SP4 and SQL2KEnt SP3a) yesterday night for Electrical maintenance. Soon after that, I checked in my Production server too. It was showing 4GB as it was earlier which I have set manualy. But when I did it in my personal computer with personal edition, the size of tempdb shrunk into 8mb.

    -Johnson
    johnson_ef, Nov 29, 2005
    #2

Share This Page