Post reply

temp db log low on space, but tempdb is Simple mode....

  • April 1, 2005 at 8:44 am

    #88034

    Hello,

    When I get the message that tempdb log is filling up, I tend not to do anything, cause tempdb is in simple mode.

     

    Off course if it's completely full, I can always back it up with no_log from the QA. But should I act when it's just low on space, like run a job that backs it up with no_log?

     

    Thanks for the insites.

  • April 1, 2005 at 9:07 am

    #550123

    I'd never want TempDB to fill up, if you're getting errors like this then you need to make sure that it is made much larger.

    I currently have 4 MDF (one for each processor) and 1 LDF files comprising my TempDB, each File is 5 GB (there's no point in having more than 1 LDF as data is written sequentially to the log file). 

    In saying that the database running on this server is > 1TB and the system is heavily used pretty much all of the time (I've never seen a situation where all of the files are being used more than 50% but don't want to shrink them because I like the built-in contingency).

    How large to make yours is a question only you can answer, it depends on how big your DB is, how much sorting is being done in queries, how many users you have and how heavily you're using tempoary tables.

    Hope this helped

  • April 1, 2005 at 3:22 pm

    #550209

    Mike, thanks for the answer. When you say "I'd never want TempDB to fill up", you're talking about tempdb log? Or data? Or both?

    I think when the log fills up, you can only access it through QA (EM will not be able to access the database). But my question is: why should I worry about the log if it's in Simple mode? It will truncate anyway when it fills up, right? Please let me know if I'm off base here.

    Thanks again

     

  • April 4, 2005 at 5:56 am

    #550285

    All of the databases I look after have alerts configured to inform me when they reach 75% log utilisation, and do an automatic backup/checkpoint (depending on their mode) when it reaches 90% full.  I know that checkpoints are done fairly often but sometimes we have way too much going on at the same time.

    If you have 1 huge transaction running that tries to clock up more room than there is in TempDBs Log then you're not going to be able to get away with the simple mode and checkpoint routine.

    I'd have a look at the busiest periods to see what size the log reaches and then double it (just to be sure), I also leave mine on autogrow but have never had to resort to letting SQL grow the file for me so far.

  • April 4, 2005 at 11:08 am

    #550384

    Progressboy,

    I think there is a basic misunderstanding about the log file here.  In Simple Recovery mode, SQL does NOT truncate the log when it fills up.  In fact a full log will stop the database from functioning at all.  In Simple Recovery mode, the completed transactions in the log are truncated whenever a Checkpoint is issued.  If your tempdb's log is filling up, it is because it has a great deal of work to do based on the size and activity in your user database(s), as Mike has already suggested.  Its most likely filling with ACTIVE transactions, not completed ones.  In this case you DO need to expand that log to prevent it from filling, because, as you have no doubt already experienced, when it does fill, tempdb stops functioning, creating big problems for your users.

    Steve

  • April 4, 2005 at 3:13 pm

    #550417

    Thanks for putting it so well

Viewing 6 posts - 1 through 5 (of 5 total)

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