Post reply

TEMP DB

  • August 20, 2007 at 11:21 pm

    #180323

    Hi Friends,

    My TempDATABASE size is incresing unnecessary i am not getting how it will be redused. Why it will be incresing may i know the reasons.

    How can get into actual size its very prob to other database plz help me

    Thanks & Regards

    subu

  • August 21, 2007 at 1:43 am

    #728204

    Subu

    Plenty of things it could be.  Search this site for "tempdb size increasing" and you'll find a lot of them.

    John

  • August 21, 2007 at 1:49 am

    #728205

    Among others temporary tables are stored in tempdb. Large and complex queries from other databases also use the tempdb. use sp_spaceused to get some basic information on how much space is used.

    Tempdb is recreated every time SQL Server starts, so you can reduce its size by restarting SQL Server. (of course this is only ok on dev machines). You can use a statement like:

    ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 10MB)

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 21, 2007 at 2:23 am

    #728212

    HI Andras & james,

    Thank u for ur reply.

    But Currently My temp DB is so big i can done alterdatabse but it will not reduse can plz give some another suggestions plz it very big prob of our development databse running quries also very slow plz help

    Regaurds

    shiv

  • August 21, 2007 at 11:38 am

    #728396

    Since tempdb is recreated when you re-start SQL services.  If it is a devl server and you can afford to restart the service - stop SQL service, rename the tempdb files, then start SQL Server Service.  New one will be created with default size

  • August 21, 2007 at 3:52 pm

    #728444

    You didn't say how big TempDB had gotten... how big is it?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 22, 2007 at 4:13 pm

    #728737

    right click and do shrink database select templog

  • August 22, 2007 at 11:13 pm

    #728779

    Is this Server also a Reporting Services Server?

    If so, are you using session caching for Reports?

    Kindest Regards,

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

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