Post reply

how to shrink temp db

  • March 12, 2012 at 6:08 am

    #391776

    All of sudden my temp db has grown drasticaly although I have 95 % free space in tempdb but no space at mount point

    Can you please guide me how can claim space from tempdb

  • March 12, 2012 at 6:29 am

    #1457787

    tempdb is recreated every time the server is stopped and started.

    the only way to reclaim the space (as far as i know) is to stop and start the service again.

    something made tempdb grow because the space was needed...it might be a developer doing select * from MillionBillionRowTable into #tmp, it might be rebuilding indexes with SORT_IN_TEMPDB, it might be just normal for your server because it needs to use temp to build up intermediate results for queries.

    tempdb might be 95% free because that process that needed a lot of results had it's table dropped...but the space is still reseerved in case it is needed again. you will have to stop and start to recover that 95% free space to the operating system.

    if it grew once to a large amount, there is a high probability it will grow to the same size again.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 12, 2012 at 6:40 am

    #1457797

    I can not SQL stop services is that only way to get space back as it is prod server

  • March 12, 2012 at 6:51 am

    #1457803

    haroon.md78 (3/12/2012)

    I can not SQL stop services is that only way to get space back as it is prod server

    Yes. The only way to get the space back is to stop and start the service.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 12, 2012 at 7:14 am

    #1457826

    Second the restart recommendation. It is documented that shrinking TempDB while in use can cause corruptions that will force a restart.

    Also you need to investigate what caused the growth, because it may well happen again. Check the TempDB-related usage DMVs

    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

  • March 12, 2012 at 7:21 am

    #1457835

    Is this code not a slove?

    USE [tempdb]

    GO

    DECLARE @space_used integer, @db_name NVARCHAR(20)

    SELECT TOP 1 @db_name = a.name, @space_used = cast(round(fileproperty(a.name,'SpaceUsed')/128.000,0) + 1 as integer)

    FROM dbo.sysfiles a

    WHERE a.filename LIKE '%.mdf'

    PRINT @space_used

    PRINT @db_name

    DBCC SHRINKFILE (@db_name , @space_used)

  • March 12, 2012 at 7:28 am

    #1457843

    That might shrink TempDB, it might do nothing (internal objects can't be moved by a shrink), it might cause corruptions that will force a restart.

    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

  • March 12, 2012 at 10:25 am

    #1457993

    Ok, thanks Gail.

  • March 12, 2012 at 10:30 am

    #1458001

    thanks for your advice i will not shrink tempb

    how can i know what caused this rapid growth

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

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