Post reply

tempdb space

  • August 9, 2011 at 3:13 pm

    #241933

    Hi,

    Can anyone give clue for the below scenario.

    Tempdb is growing high.

    Observation is the tempdev.mdf 1Gb which is acceptable but when i saw the temp.ldf it has grown upto 35 Gigs.

    I have no idea about this. Can help in figuring out why the ldf file is growing that much but actaul data file (.mdf) hardly grows to 1 gig.

    Looking for possible scenario's over here and how to troubleshoot such issues.

    I managed to shrink the ldf file back to 3 gigs.

    But i want to understand why there is a huge difference between mdf and ldf file.

    Any help would be much appreciated.

    Thanks in Advance.

  • August 9, 2011 at 3:18 pm

    #1366302

    When it ballons again run this query and post the results (or do your research, you'll find that info quite easily).

    SELECT log_reuse_wait_desc FROM sys.databases where name = 'tempdb'

    the bottom line is that you have 1 query taking massive amount of ressources and while it runs it needs that space.

    Or you have SNAPSHOT ISOLATION ON and 1 transaction is kept open for a long time. The rowversion store needs to keep all copies available from the time of the oldest transaction.

  • August 9, 2011 at 3:42 pm

    #1366308

    Hi Ninja,

    What all other possible scenario's when ldf grows beyond mdf file?

    Any example??

  • August 9, 2011 at 3:47 pm

    #1366311

    Not 100% sure so I preffer to shut up rather than give bad info.

    I know tempdb has 1 gazillion different uses (that's an actual number ;-)). What I don't know is where each different use goes (data or log).

    This might be a good read for you since you see, to love to read / research : http://msdn.microsoft.com/en-us/library/ms345368.aspx

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

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