Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ngaya
Starting Member
1 Post |
 Posted - 2007-05-21 : 11:29:44
|
| My production DB size is 12 GB total but tempdb expands to 30 GB (full harddisk size) suddenly. Any temp table or resultset would not have cause such usage, unless multiple users use them simultaneously. I am you running any administrative work like rebuilding indexes one day of the week.I want know if rebuilding indexes can cause the growth of tempdb and why ?Is there any method to trace which transaction is causing this problem? Thank |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-21 : 14:38:02
|
| Set a profiler trace. |
 |
|
|
deepakontheweb
Starting Member
14 Posts |
Posted - 2007-05-23 : 06:27:40
|
| Create a script for all indexes on your database.. and check for an option "SORT_IN_TEMPDB" BOL says:SORT_IN_TEMPDBSpecifies that the intermediate sort results used to build the index will be stored in the tempdb database. This option may reduce the time needed to create an index if tempdb is on a different set of disks than the user database, but it increases the amount of disk space used during the index build.Deepak KumarSQL Server MVP, MCDBAdeepak.kumar@sqlknowledge.comDisclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-23 : 10:39:24
|
| Can you SORT_IN_TEMPDB in sql2k? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-23 : 13:42:00
|
| "Can you SORT_IN_TEMPDB in sql2k?"Yup (well, its in SQL2000 BoL!!) |
|
|
|
|
|
|
tempdb expands to full