Post reply

Temp db log file is growing .

  • August 28, 2012 at 7:43 am

    #280483

    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..

  • August 28, 2012 at 7:50 am

    #1530811

    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

  • August 28, 2012 at 7:51 am

    #1530812

    add more space to the drive

    or

    restart SQL

  • August 28, 2012 at 7:57 am

    #1530821

    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 ?????

  • August 28, 2012 at 8:00 am

    #1530825

    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.

  • August 28, 2012 at 8:38 am

    #1530852

    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.

  • August 28, 2012 at 8:42 am

    #1530856

    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 ??

  • August 28, 2012 at 8:43 am

    #1530857

    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.

  • August 28, 2012 at 8:47 am

    #1530862

    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

  • August 29, 2012 at 4:52 am

    #1531302

    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

  • August 30, 2012 at 7:43 am

    #1531896

    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.

  • September 2, 2012 at 8:24 am

    #1532729

    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.

  • September 2, 2012 at 10:23 pm

    #1532810

    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]

  • September 3, 2012 at 12:14 am

    #1532831

    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

  • September 3, 2012 at 4:41 am

    #1532901

    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