Post reply

Tempdb size does not reduce

  • June 29, 2011 at 9:42 pm

    #389870

    Hi,

    Please help help..

    Tempdb size does not reduce even though there is not temp table in the database and unallocated space is showing 800% of the used space, Please help . Initially tempdb size was 2 MB. I explicitly created a temp table and inserted some million records in it and then close the connection. I checked the tempdb size using GUI and sp_spaceused, it became around 8 MB , i open new connection and repeated the same activity and then found tempdb size become around 16 MB. I hcke another day, there was no temp table in the tempdb but space was as it was.

    Now here my question is if temp tables do npt persists in tempdb size, then why tempdb size does not reduce once temp tables no longer exist in the database.

    Thanks in very advance, I hope someone will definitely help me out to understand this situation.

    Regards

    Ashok

  • June 30, 2011 at 12:36 am

    #1346320

    Try to shrink the data files. If this also does not work, restart SQL Server.

  • June 30, 2011 at 12:55 am

    #1346324

    a_shakeel (6/30/2011)

    Try to shrink the data files. If this also does not work, restart SQL Server.

    Nope don't shrink. Shrinking the tempdb is not good advise sometimes it'll do different things.

    Change it tempdb location where you have much spaces and do a restart the server.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • June 30, 2011 at 1:00 am

    #1346325

    ashok.faridabad1984 (6/29/2011)

    Hi,

    Please help help..

    Tempdb size does not reduce even though there is not temp table in the database and unallocated space is showing 800% of the used space, Please help . Initially tempdb size was 2 MB. I explicitly created a temp table and inserted some million records in it and then close the connection. I checked the tempdb size using GUI and sp_spaceused, it became around 8 MB , i open new connection and repeated the same activity and then found tempdb size become around 16 MB. I hcke another day, there was no temp table in the tempdb but space was as it was.

    Now here my question is if temp tables do npt persists in tempdb size, then why tempdb size does not reduce once temp tables no longer exist in the database.

    Thanks in very advance, I hope someone will definitely help me out to understand this situation.

    Regards

    Ashok

    Tempdb is not only for temp table. It has been used for lots of things.

    like Internal objects(sort,spool,hash),Version stores ect...

    Check if there is any open transaction going on using opentran.

    Also run a sp_helpdb and check the tempdb space.

    How many datafiles the tempdb have?

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 4 posts - 1 through 3 (of 3 total)

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