Post reply

Error: 9002, Severity: 17, State: 6 The log file for database tempdb is full. Back up the transaction log for the database to free up some log space

  • September 9, 2011 at 4:00 am

    #1379092

    First Check for any open transactions in Tempdb by running below query.

    DBCC OPENTRAN

    if you get below results means,start to shrink the Tempdb files.

    dbcc opentran

    --No active open transactions.

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

    dbcc shrinkfile('tempdev',sizetoshrink)

    eg.

    dbcc shrinkfile('tempdev',10)

    That's it.

    Regards

    Kumar

    http:\\Sqldbataskwithkumar.blogspot.com

    Regards,
    Kumar

  • September 9, 2011 at 4:25 am

    #1379104

    Would someone please explain to me the logic behind shrinking (making smaller) a full file?

    If the file is full, it has not free space in it. Shrinking removes free space from a file. So why, oh why do so many people suggest shrinking (removing free space) from a full file (one that has no free space)

    It's like taking a 2 litre bottle of milt and trying to put the contents into a 500ml jug.

    p.s. 7 year old thread.

    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

Viewing 2 posts - 16 through 16 (of 16 total)

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