Temp db log file is growing .

  • tempdblog file is growing to 5 gb. I dont have more space on the drive.

    want a immediate solution . without restarting the server.

    please somebody reply..

  • Add more drive space

    Figure out what's preventing TempDB's log from being reused and resolve that problem

    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
  • add more space to the drive

    or

    restart SQL

  • Thanks,

    usually it is around 2 GB. we don't have space in SAN drive to add.

    I want to release it now.. Is there any way to fix it ?????

  • As Gail mentioned, figure out what is causing the log file to grow, otherwise if it happens again you will be in the same boat.

    Something wanted the log file to grow to 5GB so if it happens again and you have shrunk the file already it will regrow to 5GB.

    Then you might get away with using DBCC SHRINKFILE

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

    Otherwise restart SQL or add more space.

  • I know one job is using tempdb.

    but tempdb is not releasing the space where as 4 Gb of space is not used.

    I am using SQL 2005. also running replication. so temp db is always in use.

  • It is not happening daily basis.. once in a quater or so I faced the problem..

    All the time I can not restart the server,,, right.

    So, please tell me is there any option to make release the unused space on tempdb ??

  • Space will not release once it has been consumed, this is due to the expensive procedure of allocating more space to files.

    If it grew to 5GB it will stay at 5GB unless you manually restart SQL or if possible run DBCC SHRINKFILE as per the article I provided earlier.

  • No, it won't release space. If the log is not growing more, leave it.

    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
  • tapaskumardm (8/28/2012)


    Thanks,

    usually it is around 2 GB. we don't have space in SAN drive to add.

    I want to release it now.. Is there any way to fix it ?????

    any problem in using DBCC SHRINKFILE?

    Regards
    Durai Nagarajan

  • TempDB automatically re-uses space. So, if the transaction log continues to grow it is because it has rows which may not be deleted due to open transactions.

    I have found that using connection pooling in ADO if you don't close your connection, the connection pool won't complete implicit transactions. This results in SQL Transactions that remain. Until the transaction is committed, or rolled back, the transaction log cannot re-use this space.

    Since you say this only happens once a quarter I would guess the ADO issue may not be the cause. You may have a longer running job requiring more time and thus grows tempdb transaction log.

    If you find this is the case, then consider taking work for larger jobs and breaking them down into smaller batches.

    Finally, make sure CheckPoint is at a short enough interval. This is what causes SQL Server to go and remove completed transactions from transaction logs of databases in simple recovery mode. TempDB is always in simple recovery mode.

  • What I do is create a 2 or 5Gb dummy file on each SQL disk (ldf,mdf,tmpdb). Whenever there is an urgent disk storage problem I can delete the dummy file and buy myself some time. It is no final solution but it helped me out some a few time.

  • durai nagarajan (8/29/2012)


    any problem in using DBCC SHRINKFILE?

    Yes, using DBCC Shrink[File|Database] on a live tempdb has in some cases caused corruption.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Hi,

    In your case you can use DBCC SHRINKFILE if it doesn't worked out try once again, then look for log space if the reduction is not much satisfied then use the query

    select name,log_reuse_wait_desc form sys.databases where dbname='<dbname>' look for the rows that query returns. if it is active transaction identify the spid and troubleshoot or kill the transaction. If you dont have permission to kill then you can use DBCC FREEPROCCACHE---> this will release the plan cache in the memory and gives some space

  • I think DBCC SHRINKFILE is worth to use.

    It will make some space because in log file there is active and inactive portion.You can reclaim inactive space in log file.

Viewing 15 posts - 1 through 15 (of 22 total)

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