Post reply

TempDB Log Growth

  • August 10, 2007 at 8:07 am

    #726030

    Sorry, that is 2005 syntax.

    USE

    TEMPDB

    GO

    SELECT

    OBJECT_NAME(id),rowcnt FROM sysindexes

    WHERE

    ROWCNT > 0 AND rowcnt IS NOT NULL

    AND

    OBJECT_NAME(id) LIKE '#%'

    This should work with 2000

  • August 10, 2007 at 8:12 am

    #726035

    returns no rows I'm afraid

  • August 10, 2007 at 12:36 pm

    #726140

    Very strange.

    If you can truncate it manually then SQL Server already knws that there are no open or uncommited transactions... So Checkpoint SHOULD be cleaning up!

    [font="Verdana"]-- Wally
    -- Reluctant DBA[/font]

  • August 10, 2007 at 1:29 pm

    #726155

    If there are no rows, and you can truncate it, then something/someone is creating REALLY large temp files, but they are cleaning up after themselves. 

    Since it appears to have started recently, then I will have to assume that this is new code that went out.    Do I have to say change control?  The query I gave should return rows whenever this table is active, so you should be able to create an alert with a fix it job to do two things. 

    1) run this query and save it to a Real table.  Get an idea how much data we are talking about.

    2) Possibly initiate a job to come back later and shink it again.

Viewing 4 posts - 16 through 18 (of 18 total)

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