none
tempdb log file not releasing space RRS feed

  • Question

  • 2008R2 64bit

    I have an issue (and a bunch of questions too!) so any help would be very appreciated.

    The basic issue is the tempdb log is not release space and continues to grow.  I've done a lot of research so far so I just need help determining if I am on the right track.

    We have our tempdb split into multiple files (per cpu) and each initially sized, although I don't think the same is for the tempdb log file.  Currently each tempdb data file is 6GB in size, and the log is 25GB.  According to SQL the data files are all pretty much free space ~99%), but the log is reporting a negative number (bug?) in SSMS but if I run:

    dbcc sqlperf(logspace)

    I get:

    tempdb 23923.18 42.8968 0

    So I am going to assume that 42% log used is correct.  It's been like this for hours so I think there is an issue.  I've check the number of pages stored in the tempdb for user/system/store is it's VERY minimal (couple hundred mb's) so I think this is an issue with my log file not flushing out. 

    I ran:

    DBCC LOGINFO

    And noticed 2's toward the beginning of the log, then tons of 0's, then more 2's at the end.  My understanding is that means those particular sections are still in the virtual log and therefore the space cannot be reused.  From here I tried to see what (if any) causes there may be to cause this scenario so I ran:

    SELECT Name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases

    From this I get back a pretty promising result:

    tempdb SIMPLE ACTIVE_TRANSACTION

    Seems like it's waiting on an active transaction, but when I check DBCC OPENTRAN it reports no active open transactions.  At this point I am unsure of what to check next.

    So how is that it's waiting on an active transaction, but according to SQL there's no active transactions???

    Any ideas?



    • Edited by dereksg Thursday, December 8, 2011 6:53 PM
    Thursday, December 8, 2011 4:56 PM

Answers

  • It is my understanding that the log will shrink down and manage itself:

    http://msdn.microsoft.com/en-us/library/ms175527.aspx

    "Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small."

    Maybe my understanding is totally of though, I'm just really trying to get a handle on what's happening internally as this "issue" only occurs on this one system (all other logs seem to grow/shrink fine).

    Anyways I think my issue maybe is multifaceted because throughout this investigation I have found a few different issues...one I think is long running jobs that create a lot of tempdb objects, the other is a service the programers setup that keeps a connection open to the TempDB and runs commands periodically.  This also creates lots of pages in the TempDB...and I *think* it's causing the tempdb log to grow more than it should by keeping objects active in the VL.  When I run dbcc opentran it states there are no open transactions, however, when I check what the log is waiting on to reclaim space it says "active transaction".

    Anyways thank you very much for your help in this matter and helping me understand what's at work here!

     

    Wednesday, December 21, 2011 12:44 PM

All replies

  • A checkpoint is applied automatically when tempdb log size is at 70% full.

    There are many reasons for tempdb log to grow and only after proper study and observation you can size tempdb to a specific size which is interms with your space constraints.

    There is a good post which says about Tempdb monitoring and space usage,Please read it http://blogs.msdn.com/b/deepakbi/archive/2010/04/14/monitoring-tempdb-transactions-and-space-usage.aspx

    Thank you, 


    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Thursday, December 8, 2011 7:43 PM
  • Hi Derek,

    You are not able to shrink the log file, if the vlaue of 2 is shwoing bottom on the O/P for DBCC LOGINFO

    Please refer the below links:

    http://msdn.microsoft.com/en-us/library/ms176029.aspx

    http://support.microsoft.com/kb/307487

    Thanks,


    Thanks, Satish Kumar.
    Thursday, December 8, 2011 7:51 PM
  • Why do the log_reuse_wait_desc say 'ACTIVE_TRANSACTION' yet DBCC OPENTRAN report no active open transactions?   Based on what I have seen I believe there is an active transaction incorrectly keeping records in the VL (state 2) causing the log not able to reuse space.

     

    Thursday, December 8, 2011 7:58 PM
  • Hi Dereksg,

    Did you check the tempdb, which has log_reuse_wait_desc=active_transaction, but you used dbcc opentran on tempdb returns no open transaction?

    If so, there might be because of the tempdb is NOT CHECKPOINT’ing correctly. The CHECKPOINT in the tempdb happens only when the log if 70% full. So try to run CHECKPOINT manually. Try running CHECKPOINT manually few times and see if that helps.

    More information, please refer to this blog
     
     
     

    Regards, Amber zhang
    • Proposed as answer by Informax Friday, July 26, 2013 11:37 AM
    Monday, December 12, 2011 8:27 AM
    Moderator
  • Yes I did run both of those commands (as stated in the original post) and that's exactly the results I am getting.  Is there something I can do (database log growth, initial size, ect) to prevent this scenario?  I can manually run check point and of course shrink down the log again but I'd obviously prefer the instance to manage itself in this regard.

    I guess I don't understand why it's growing so huge if it's only 40% utilized? 
    • Edited by dereksg Monday, December 12, 2011 6:27 PM
    Monday, December 12, 2011 12:59 PM
  • You may have some process running that is causing this. In the past I had a system that was utilising Service Broker. All of a sudden we were running out of space on the disk due to the consumption by tempdb. This was growing at a very rapid rate of nots.

    After some investigation it turned out that the messages in Service broker were not being terminated and as such every message was being kept. Once we found the process and corrected this corrected the issue.

    Have a look at this link to help you determine what is consuming your space in tempdb - http://msdn.microsoft.com/en-us/library/ms176029.aspx

    I hope this helps.


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Monday, December 12, 2011 7:40 PM
  • I think you may be on to something there...when I run:

    SELECT top 5 *
    FROM sys.dm_db_session_space_usage
    ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC

    I get back a session_id that has 18247049 pages allocated but only 4120477 deallocated...the way I understand it is the difference is the number of pages still held?

    When I do a sp_who2 on this session_id the status for this process is 'sleeping' and the program name is 'Message Queue'...that's related to the service broker?

    If this is similar to the issue you had how did you resolve it?

    Thanks!

    *edit*

    Actually, that program is from an internally developed application that calls the server from a web host.  From what I understand this application calls heavy duty sprocs and the connection is held open.  With that said shouldn't the alloc/dealloc be similar and if not these pages are related to the issues I am experiencing?  Am I still on the right track here though?

    I'm thinking the best way to tackle this is to have the developers to modify the application and force it to call EXEC sp_reset_connection periodically?



    • Edited by dereksg Tuesday, December 13, 2011 3:59 PM
    Tuesday, December 13, 2011 3:40 PM
  • Have a look at this link - http://www.sqlteam.com/article/how-to-troubleshoot-service-broker-problems it gives you some great details on investigating Service Broker. I expect it is similar to the issue I faced in that you will need to find all of your conversation points and ensure that you converstations are being terminated.

    To end a conversation have a look at this link. It will help you script this up as you will more than likely have quite a few conversations sitting around - http://msdn.microsoft.com/en-us/library/ms177521.aspx

    I hope this helps 


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    • Marked as answer by amber zhangModerator Friday, December 16, 2011 6:50 AM
    • Unmarked as answer by dereksg Tuesday, December 20, 2011 8:06 PM
    Tuesday, December 13, 2011 7:02 PM
  • It's not the Service Broker, but thanks for the links.  I've been really trying to understand what's at work here but can't seem to understand why the log never shrinks down.  I added logging to assist and maybe this will help in shedding light what might be the problem, or where I am not understanding what's at work here.

    Every 10 mins I have recorded the tempdb log's size and free space:

    RecordTime                        LogSpace    SpaceUsed(%)    Status
    2011-12-20 14:51:32.240    13338.87    6.15    0
    2011-12-20 14:41:26.933    13338.87    5.84    0
    2011-12-20 14:31:33.040    13338.87    5.60    0
    2011-12-20 14:21:28.353    13338.87    5.26    0
    2011-12-20 14:11:32.093    13338.87    4.69    0
    2011-12-20 14:01:27.183    13338.87    4.38    0
    2011-12-20 13:51:32.267    13338.87    3.45    0
    2011-12-20 13:41:26.900    13338.87    3.14    0
    2011-12-20 13:31:38.903    13338.87    2.72    0
    2011-12-20 13:21:29.170    13338.87    2.53    0
    2011-12-20 13:11:32.570    13338.87    2.28    0
    2011-12-20 13:01:27.017    13338.87    2.03    0
    2011-12-20 12:51:32.253    13338.87    0.24    0
    2011-12-20 12:41:26.740    13338.87    69.68    0
    2011-12-20 12:31:32.783    13338.87    69.24    0
    2011-12-20 12:21:26.690    13338.87    68.97    0
    2011-12-20 12:11:34.880    13338.87    68.60    0
    2011-12-20 12:01:26.740    13338.87    68.06    0
    2011-12-20 11:51:31.527    13338.87    67.16    0
    2011-12-20 11:41:27.203    13338.87    66.78    0
    2011-12-20 11:31:32.337    13338.87    66.55    0
    2011-12-20 11:21:26.603    13338.87    66.29    0
    2011-12-20 11:11:35.540    13338.87    65.97    0
    2011-12-20 11:01:37.823    13338.87    65.37    0
    2011-12-20 10:51:31.483    13338.87    64.38    0
    2011-12-20 10:41:27.250    13338.87    63.98    0
    2011-12-20 10:31:32.237    13338.87    63.54    0
    2011-12-20 10:21:26.800    13338.87    63.21    0
    2011-12-20 10:11:32.090    13338.87    62.78    0
    2011-12-20 10:01:27.013    13338.87    62.32    0
    2011-12-20 09:51:31.820    13338.87    61.36    0
    2011-12-20 09:41:42.557    13338.87    60.89    0
    2011-12-20 09:31:32.427    13338.87    60.18    0
    2011-12-20 09:21:26.647    13338.87    59.42    0
    2011-12-20 09:11:31.867    13338.87    58.83    0
    2011-12-20 09:01:35.617    13338.87    56.25    0
    2011-12-20 08:51:31.320    13338.87    55.27    0
    2011-12-20 08:41:26.493    13338.87    55.07    0
    2011-12-20 08:31:32.317    13338.87    54.89    0
    2011-12-20 08:21:26.497    13338.87    54.65    0
    2011-12-20 08:11:31.593    13338.87    54.18    0
    2011-12-20 08:01:26.933    13338.87    53.57    0
    2011-12-20 07:51:31.460    13338.87    52.48    0
    2011-12-20 07:41:26.340    13338.87    52.05    0
    2011-12-20 07:31:38.287    13338.87    49.89    0
    *snip*

    (I have days worth now lol)

    As you can see the log free space gradually grows (well...shrinks), and at 70% a checkpoint is done by SQL.  The space is returned as free space, however, the log size never actually shrinks.



    • Edited by dereksg Tuesday, December 20, 2011 8:20 PM
    Tuesday, December 20, 2011 8:01 PM
  • Can you clarify somethign for me. Maybe i missed this originally so sorry. Are you concerned that the physical size of TempDB is not shrinking even though the available free space internally is available ?

    If that is the case then the actual physical size of your tempdb will not shrink and release the space back to the OS. When you do a restart of your Instance of SQL Server this will re-create the TempDB database. The size will be x. Which is what is has been set at being for the initial creation. (look at the properties of TempDB to see the initial size, growth etc). As difference processes require it TempDB will consume more physical disk and the file will grow. Internally it may have 85 % free space. This is not an issue. The issue is if you are continually growing in size (physical disk usage) as well as not having any free space internally.

    I hope this helps


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Tuesday, December 20, 2011 11:02 PM
  • It is my understanding that the log will shrink down and manage itself:

    http://msdn.microsoft.com/en-us/library/ms175527.aspx

    "Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small."

    Maybe my understanding is totally of though, I'm just really trying to get a handle on what's happening internally as this "issue" only occurs on this one system (all other logs seem to grow/shrink fine).

    Anyways I think my issue maybe is multifaceted because throughout this investigation I have found a few different issues...one I think is long running jobs that create a lot of tempdb objects, the other is a service the programers setup that keeps a connection open to the TempDB and runs commands periodically.  This also creates lots of pages in the TempDB...and I *think* it's causing the tempdb log to grow more than it should by keeping objects active in the VL.  When I run dbcc opentran it states there are no open transactions, however, when I check what the log is waiting on to reclaim space it says "active transaction".

    Anyways thank you very much for your help in this matter and helping me understand what's at work here!

     

    Wednesday, December 21, 2011 12:44 PM
  •  

    Hi Derek,

    I'm afraid tempdb is not behaving as you expect because Autoshrink option is OFF. The behaviour you are expecting for SIMPLE recovery model databases about autoreducing it's file size is true if you have "Auto shrink" option ON per database. This option is what makes from time to time auto reduce phisical file size (OS size) when free virtual log space is over 25% (http://msdn.microsoft.com/en-us/library/ms178037.aspx). I'm afraid tempdb has Auto shrink OFF by default, and I don't know if that can be changed (I'm in front of an instance of SQL Server 2000 and this option is read only, don't know in 2008...). You can not make a backup of tempdb for instance. tempdb is a very special SIMPLE database.

    As you have noticed, tempdb virtual log files are freed when % log space used reaches 70% because SQL Server engine fires an automatically CHECKPOINT command, because that's the way SQL Server engine works (with tempdb "Recovery interval" minutes has nothing to do neither because tempdb is not recoverable). However, CHECKPOINT doesn't mean that file size will be reduced. Actually, it won't; unless Autoshrink is ON and, if it is, I'm not sure this will happen immediately.

    Have a look at that note in http://msdn.microsoft.com/en-us/library/ms189085.aspx

    How Log Truncation Works -> Truncation does not reduce the size of a physical log file. Reducing the physical size of a log file requires shrinking the file. For information about shrinking the size of the physical log file, see Shrinking the Transaction Log.

    Regarding your Tuesday, December 20, 2011 8:01 PM records, I think your SQL engine is working properly, because it frees virtual log space when it reaches 70%. File size is not reduced because CHECKPOINT doesn't do that. In order to reduce tempdb files, you must perform a DBCC SHRINKFILE command or restart MSSQLSERVER service.

    In my opinion, 13GB of tempdb could perfectly be the size your system needs in order to work properly (space needed for reindex in maintenance plans, sorting queries, ...). If tempdb keeps the size steady, it's ok. The problem is when it keeps growing. Autoreducing it you'll just get sizes between 0GB and 13GB from time to time, but in the worst case you will always need these 13GB because it's a your system behaviour requirement, so let them to be there always and don't get stuck for it.

    In order to see if your tempdb has Autoshrink ON, check the following in your SQL instance

    SELECT
      [name] as [DB name],
      [recovery_model_desc] AS [Recovery model],
      CASE [is_auto_shrink_on] WHEN 1 THEN 'Yes' ELSE 'No' END [AutoShrinkON]
    FROM master.sys.databases
    GO

    and try the following on your own risk modifying this property. I've never tried it as Auto Shrink as read only property!

    ALTER DATABASE tempdb SET AUTO_SHRINK ON   -- I've never done that! Hope it works...
    GO

    Looking forward to hearing from your results.


    • Edited by Jordi Marquez Friday, December 30, 2011 4:35 PM add link to how log truncation works
    Friday, December 30, 2011 4:29 PM
  • Hi,


    Before Alter Database to Auto shrink On Property please check the below link. It might cause down sides and unnecessary complication's with the Performance.

     

    http://support.microsoft.com/kb/315512

    Also Read the below Blog:

    http://blogs.msdn.com/b/buckwoody/archive/2009/07/01/sql-server-best-practices-auto-shrink-should-be-off.aspx

     


    Regards JakDBA Please Mark this as Answered if this solves your issue or satisfied with the response.
    • Edited by SQLServer2k8 Wednesday, February 1, 2012 4:25 PM
    Wednesday, February 1, 2012 4:25 PM
  • Hi,

    That link says:

    Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

    It will not shirnk the file back down - but it will checkpoint and empty out data. which is why your example went from 60-70% full then down to 2%.

    You have processes on your system which have caused tempDB to grow - if you shrink tempdb those processes will cause it to grow again. Becasue they are going to have to wait for that growth they will take longer than if you left TempDB at the larger size.

    you should also check teh autogrowth settign - is it growing at a rate of % or MB. If set to % it will grow in ever larger chunks - eg 10% of 10gb is larger than 10% of 9Gb. if you set it to grwo at 256MB it will always allocate 256MB.

    Seth


    http://lqqsql.wordpress.com

    Monday, March 5, 2012 3:25 PM
  • I think this may be a partial answer but it is probably not going to do anyone any good. Simple mode is the default on this and the chances that it is anything else is fairly slim. There are things that can set this up... for example, a very large - say 32 plug GIG of RAM with 4 Quad processors - cache can cause uncommitted temp table calls to spill over into the tempdb which would cause the tempdb log_reuse_wait_desc to display as ACTIVE_TRANSACTION. The better answer, IMHO, would be to search for Procedures and Functions that use temp tables (code too if that is the case) where a temp table is not properly dropped, particularly if that temp table is involved in a transaction. For example, you begin tran... open a temp table, run a check, check fails, the transaction is rolled back. If you don't drop the temp table, it will remain open and the check point will attempt to hold onto everything from there going forward. What I would like to see as an answer for this is a method that would allow one to look into the transaction that is active and roll that back - if that is possible in the temp table. It is a ridiculous state... ridiculous because it is caused by poor programming that is a legacy one inherits. Eventually, as the "answer" implies, the ACTIVE_TRANSACTION will go away, but in the meantime, if you are hurting for disk space, you're pretty much at the mercy of the garbage disposal. Not good... is there a better solution for this in SQL 2012? We're running SQL 2008 R2 and we see this once of twice a week.

    R, J

    Monday, March 19, 2012 1:22 PM