tempdb transaction log file growth - SQL Server 2000

  • Transaction log file of the tempdb is growing quickly and causing a drive space issue for me. As a short term solution my DBAs have created a job that truncates the transaction log file periodically. I do not know the reason for the abnormal file growth.

    Objective

    a) I would like to fiund out what exactly is causing this.

    Question:

    a) How do I know what is causing this issue?

    I would like to pin point the cause. Any script, tool will be very helpful. It's SQL 2000 environment running on win2k3.

    Please post if you need any more information.

    Thank you in advance for your help.

    masroof

  • SQL Server has three recovery models. Each recovery model defines how transactions are dealt with in the transaction log.

    Simple Recovery: transactions are cleared automatically so the space can be reused.

    Full Recovery: transactions are kept until a log backup is performed.

    Bulk-Log Recover: same as full, except certain bulk operations are minimally logged.

    If your database is setup in simple recovery, the transaction log will grow to the largest size needed. There is no need to perform backups (in fact, you can't).

    If your database is setup in full recovery or bulk-logged, the transaction log will continue to grow until you perform a backup. If you are not currently backing up the transaction log you can either switch the database to simple or implement log backups. Log backups should be performed as frequently as required by the business requirements. This can be anything from every minute up to once a day, but is usually something like every 15\30\60 minutes.

    You should not implement a process that truncates and shrinks the log files. This will cause performance issues because the transaction log will just grow again and the constant truncating\shrinking will cause fragmentation.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • tempdb should always be in simple mode. If it's growing large, you have a large transaction, or open transactions.

    dbcc opentran will look for open ones.

    For large ones, not much you can do unless you can break into multiple transactions. Any idea what uses a lot of tempdb space? Any large sorts?

  • Tempdb Recovery Model Is SET to SIMPLE & Cannot Be Modified.

    Refer : ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ce4053fb-e37a-4851-b711-8e504059a780.htm

    Also Refer : For other questions

    http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

  • Hi All,

    Recently I came across the same issue on one of our client server.

    The tempdb started increasing at a rate of 3MB/sec.Then i transferred the growth of it to another drive through secondary datafile.But still it started eating that drives space and as a SHORT TERM resolution to stop the growth of the tempdb we rebooted the server.

    Can anyone provide me why this happens and how to avoid it permenantly??

    I can provide you the following details about my server.

    1.'tempdb' is in SIMPLE recovery mode itself.

    2. There are no open transactions running at that time.

    3.Even to the new drive where I kept secondary logfiles is not having drivespace issue also.

    4.'tempdb' is set to grow 'Unrestricted'.

    5. No Critical errors have been noticed in EVENT LOGS.

    Please help me why this sudden growth may happen and how to avoid it.

    Thanks in Advance

    Vinu

  • I used to work somewhere where SQL2k would occasionally do this...

    No open transactions, truncate log & shrinkdb would do nothing. In the end all we could do was keep an eye out for it and when tempdb started growing we would schedule a restart of the DB service that evening.

    Never got to the bottom of it 🙁

  • Hi,

    i want a script that it should notify us when any opentransactions running more than 30 seconds

  • Hey Steve,

    I know this is an old posting but the problem of tempdb log growth seems to be a perennial issue.

    Why should tempdb be set to SIMPLE recovery model?

    In SQL 2000 what is the best way to find out why it is growing so big?

  • peter-970097 (2/11/2010)


    Hey Steve,

    I know this is an old posting but the problem of tempdb log growth seems to be a perennial issue.

    Why should tempdb be set to SIMPLE recovery model?

    In SQL 2000 what is the best way to find out why it is growing so big?

    Hi Peter,

    I am going through a similar situation, I have an SP that is failing during an insertation into a temporary table. Here are some commands that you can run in single or in a query:

    use tempdb

    go

    -- Reports information about a specified database or all databases.

    exec sp_helpdb 'tempdb'

    go

    -- Displays the number of rows, disk space reserved, and disk space used by a table in the current database, or displays the disk space reserved and used by the entire database.

    EXEC sp_spaceused

    Go

    -- Review tempdb database system objects

    SELECT name

    FROM tempdb..sysobjects

    Go

    -- Undocumentated command from microsoft

    dbcc showfilestats

    go

    -- Checks the consistency of disk space allocation structures for a specified database

    dbcc checkalloc ('tempdb')

    go

    -- Checks the allocation and structural integrity of all tables (in the current database) in the specified filegroup.

    DBCC CHECKFILEGROUP

    go

    -- Displays fragmentation information for the data and indexes of the specified table.

    dbcc showcontig

    go

    -- Provides statistics about the use of transaction-log space in all databases.

    dbcc sqlperf(logspace)

    go

  • This is old story, but does anybody has ever resolved this?

    As we know checkpoint on tempdb is issued when logfile gets filled by 70%.

    I'm executing:

    dbcc opentran

    DBCC SQLPERF(logspace)

    dbcc opentran

    And here is what I receive:

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Database Name Log Size (MB) Log Space Used (%) Status

    -----------------------------------------------------------------------

    tempdb 556.80469 95.857361 0

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    It looks like for some reason checkpoint is not issued.

    Any ideas how to fix this?

  • Are you sure that checkpoint is not getting issued or could it be that checkpoint is getting issued but the log space is not getting marked reusable?

    http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I think so. Because if I do it myself, on next moment I get something like this:

    Database Name Log Size (MB) Log Space Used (%) Status

    -------------------------------------------------------------------------------

    tempdb 556.80469 1.9500393 0

  • This is a somewhat 'known' issue with the tempdb transaction log. By default the system does not truncate the completed transactions in the tempdb transaction log until it hits 70% usage (that's buried in BOL somewhere). Unfortunately this can cause a 'race' condition for the cleaning out of the tempdb transaction log and applications filling it up. True a checkpoint should clean it out but does not all of the time - sometimes you'll need to do a backup transaction/truncate_only on tempdb to clean things up.

    We have implemented a script that runs every 15 minutes using DBCC SQLPERF(LOGSPACE) coupled with logic to truncate the inactive part of the tempdb log just for this type of situation. It emails us when it happens at 40% and pages us when it hits 85% - yes we do have some 'killer' transactions ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 13 posts - 1 through 12 (of 12 total)

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