Post reply

TempDB datafile is Huge

  • January 7, 2008 at 8:14 am

    #154045

    Hi All,

    Temp DB data file is huge on one of our Production Server.I try to shrink the database

    as well as Shrinkfile .But no lock.There is no open transaction in TempDB.

    USE [tempdb]

    GO

    DBCC SHRINKFILE (N'tempdev' , 672)

    GO

    This is the message i got when i run the above command

    DBCC SHRINKFILE: Page 1:1957112 could not be moved because it is a work table page.

    (1 row(s) affected)

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

    Currently Temp DB is around 15.5 GB and around 14GB is Unused.Please

    let me know how can i reclaim the Unused space??

    Out put of the sp_spacedused

    database_name database_size unallocated space

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

    tempdb 15370.50 MB 14525.02 MB

    reserved data index_size unused

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

    783344 KB 758864 KB 9000 KB 15480 KB

    Any idea how can i shrink the tempDb??

    Thanks

    Hammad khan

  • January 7, 2008 at 8:34 am

    #765368

    Refer to the following post on MSDN:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1890772&SiteID=1

    Use the query in the post to list the objects in TempDB, this will help you troubleshoot the issue. TempDB is volatile, worst case restart SQL Server and run a DBCC SHRINKFILE('Tempdev',truncateonly). Please note however this isn't a recommended practice. You should forecast your space requirements for TempDB and size appropriately. Please refer to this article under the section "TempDB Space Requirements".

    http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

    Tommy

    Follow @sqlscribe

  • January 8, 2008 at 1:19 am

    #765607

    any large data updations going on? r u using gloabal temp tables.

    "Keep Trying"

  • January 9, 2008 at 3:02 am

    #766069

    If your tempdb has grown to 15 GB, then something must have run that needs that space. The only question is if it was a one-off process.

    If you can be certain that the offending process will not be run again within 3 months, then go ahead and shrink tempdb.

    If it is likely the process will run at least once per month, then you ned to plan for a tempdb size of 15 GB, and allow for growth over time as your business grows.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • January 9, 2008 at 4:21 am

    #766089

    Maybe you could setup a server side trace on database growth for the tempdb database...

    That might help finding out who is requiring that space.

  • January 9, 2008 at 4:29 am

    #766092

    Hammad khan (1/7/2008)

    Hi All,

    Temp DB data file is huge on one of our Production Server.I try to shrink the database

    as well as Shrinkfile .But no lock.There is no open transaction in TempDB.

    USE [tempdb]

    GO

    DBCC SHRINKFILE (N'tempdev' , 672)

    GO

    This is the message i got when i run the above command

    DBCC SHRINKFILE: Page 1:1957112 could not be moved because it is a work table page.

    (1 row(s) affected)

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

    There are three methods for shrinking tempdb all of which are explained with examples in the following article.

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

    CodeMinkey

  • March 11, 2009 at 4:17 am

    #957340

    I am having the same issue.

    I know that this issue is corrected when I restart the sql server, but am not allowed to do that because this is a production server.

    I'll be happy to know if you already have a solution to this problem.

    Peter

  • June 29, 2011 at 1:47 am

    #1345695

    Hi Experts..

    I have a same kind of situation here..

    TempDB is growing enormously and we are getting alerts continuously..

    I have tried to check the following but and found nothing.

    -open transactions on the DBs

    -Checked for any jobs like reorg/rebuid indexes.

    -Active sessions on DBs

    - Used DMvs to check for any long running queries consuming resources.

    The only thing I observed other than the above is there are lots of Sleeping sessions on databases. Could that be a issue for growing tempdb? I so, how can I know which process is using tempdb.

    How to know what all databases/processes are using tempdb? is there any query or method to check?

    Please can any one throw some light on this issue so that I can troubleshoot the issue in future.. Luckily the usage has come down after sometime..

    Thanks in advance..

    KKK

  • June 29, 2011 at 2:55 am

    #1345728

    Personally, I would look for "nasty" query doing far too many page reads or something like that.

    To find that out, I would set up a server side trace based on query duration (that should do).

    To find out how to do that, I can only recommend to buy "Inside SQL Server: TSQL Querying" by Itzik Ben Gan. Awesome book!

    http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2008/dp/0735626030/ref=sr_1_1?s=books&ie=UTF8&qid=1309337611&sr=1-1

    I only read the 2005 version of the book but the 2008 one can't be bad either...

    I don't know if I am an expert but this book made me (much) better at TSQL

    Cheers

    Eric

Viewing 9 posts - 1 through 8 (of 8 total)

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