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
Are you doing bulk insert/update/delete or temp tables are involved in your application!! Refer below links http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx http://support.microsoft.com/kb/307487 http://support.microsoft.com/kb/317375/
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?
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
In what way you are inserting these rows? What is the availabe free space in tempdb before and after this job?