Post reply

5 GB TempDB Database

  • November 27, 2003 at 10:36 pm

    #160199

    Hi All

    I need your help on TempDB database. The database size of TempDB has grown to 5 GB on my server.

    Even i am not using any temp. objects and have not created indexes with SORT IN TEMPDB option but still i am not able to control the database size. I have 4 other databases on this server with 1 GB database size each.

    I have tried restarting SQL Server after performing standard tuning options, but that didn't help.

    I am also getting hourly data update from a Third Party Tool, but this on a different database and i think its not using TempDB in any condition.

    My server is configured with MS SQL Server 2000 Enterprise Edition on MS Windows 2000 Server, 2 GB RAM, 250 GB Hard disk, 2 GHz Pentium Dual CPU. All other databases are of normal size (Master, Model, MSDB)

    Please post a checklist for TempDB database size, so that i can check everything on the server.

    Please help me.

    Mohit

    Thanks
    Mohit Nayyar
    http://mohitnayyar.blogspot.com/
    "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

  • November 27, 2003 at 11:27 pm

    #483960

    Hi there

    This is a tricky one, do you run long transactions? or numerous extensions to data files? here is a small example, this code increases my tempdb from 50Mb to 2.5Gb...

    loop 50 times..

    insert into mytable select * from myothertable

    end loop

    not offical t-sql code of course 🙂 now an explain plan shows me that a "table spool/eager spool" is happening, where is uses the tempdb to optimise rewinds.

    Now, If I whack on a clustered index (non clustered wont help), this disappears... and no huge tempdb

    So this may be your issue and is something to lookout for in profiler.

    Cheers

    Ck

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • November 27, 2003 at 11:51 pm

    #483964

    Thanks Chris

    But i am not using this statement anywhere in my database. And now my problem is of database SIZE. How do i reduce the size, so it will be of normal TempDB database size.

    Please help

    Mohit

    Thanks
    Mohit Nayyar
    http://mohitnayyar.blogspot.com/
    "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

  • November 28, 2003 at 2:09 am

    #483974

    Is it taking up total 5 GB or any space is available on the tempdb database ?

Viewing 4 posts - 1 through 3 (of 3 total)

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