SQL Server Performance

TempDB issue(incresing file size issue)

Discussion in 'Performance Tuning for DBAs' started by abhishek.goel123, Jan 18, 2009.

  1. abhishek.goel123 New Member

    Hi all,
    I am having issue with tempdb . Initially i was having a 120gb drive and a four processor machine.
    I have splitt the tempdb into 4 files intially with with default size of 14 gb with 10% unlimited growth. Then in next cycle increased the size of the file to 24 gb each.
    Now the process has started taking more time and the process which was taking 2 hr 30 has gone to 29 hr.
    i cannot figure out if some other setting is altered . Could anyone please suggest . It is urgent !!
    Thanks in advance.
    Abhishek
  2. ghemant Moderator

  3. moh_hassan20 New Member

    set the size of tempdb to be 30-40% of max size database.
    it means if you database 10G , tempdb is 10 * 0.4 = 4 GB
    create 4 datafiles , with 1GB each , and one log file with size 1 GB (log file with 25% of tempdb size) with auto 100MB.
    if you can , distribute the datafiles over different physical disks (with different spindle)
    Enabling Instant Data File Initialization , for speeding database creation / autogrow
    can i know what the size of you database?
  4. abhishek.goel123 New Member

    Hi ,
    I am having a database of 55 GB size and the jobs use a lot of tempdb database in one job cycle it inserts 242 million records .
    I had tempdb drive of 120 gb and i am not sure that is there any issue in allocating more size to the tempdb files?
    Now we have split the tempdb files in 14 GB each .
    Thanks
    Abhishek
  5. satya Moderator

    In what way you are inserting these rows?
    What is the availabe free space in tempdb before and after this job?

Share This Page