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 |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2007-03-19 : 15:14:15
|
| Is ther any other method of resetting TmpDB memory/size?I have a huge ETL job which grows tempdb tremendously and does not bring it back to normal after completing the job. I need to restart the SQL service to reset it.Any other ideas??TIA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-19 : 15:24:31
|
| Can't you just shrink it?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-19 : 16:04:34
|
"Can't you just shrink it?"Needs to be a single-user type operation in SQL Server 2000 AFAIK ... (i.e. might as well stop/start the SQL Service  )Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-19 : 16:11:23
|
| The shrink of tempdb is usually very fast and requires no downtime for the other databases, so I wouldn't recommend stop/start of the service.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-03-19 : 16:25:32
|
| If you have the space, why not just leave it alone?CODO ERGO SUM |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2007-03-19 : 17:00:20
|
| No, I cannot and perhaps should not allow it to grow at this rate and not claim back the space. I will try shrink db.Any best practices for shrink db? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-19 : 17:33:49
|
| If you prevent tempdb from growing, then you may cause things to break if it needs more space but is unable to get it.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-20 : 16:02:24
|
| I've never done it with SQL Server in single user mode. I've always been successful in the past just using dbcc shrinkfile. Perhaps I was lucky that no process was accessing tempdb at the time.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
How to release TempDB memory