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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2006-11-30 : 04:47:47
|
| I don't really know much about the tempdb and how it works, just have done some brief reading on it. Recently I have been having some problems with it growing quite large, as we don't have as much HD space on our DB server as we would like to.One thing I think is making it worse is a weekly task that runs that ends up executing thi SP about 600,000 times in a loop. This SP selects rows into a temp table.Would this temp table be the cause of the growing tempdb ? Just curious, not sure what I can do about it :)Thanks again!mike123 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 04:52:24
|
| "Would this temp table be the cause of the growing tempdb ? "It shouldn't be - because the Temp Table is local to a single execution of the Sproc. So when the Sproc finishes the Temp Table will be released.However, if something is selecting a lot of data into a Temp Table, then that would extent TEMPDB.Also large queries themselves can cause intermediate data to be temporarily stored in TEMPDB, and I think some maintenance routines (e.g. rebuild indexes) can use TEMPDB too.If you stop-start SQL Service TEMPDB will be recreated - so that might be a way of keeping it under control, but basically if you need a maximum size of, say, 2GB to get through the week then you might as well leave it at that size. Or find the query that is making it that large, and refactor it.Kristen |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2006-11-30 : 05:02:17
|
| Hi Kristen,It's growing about 10gb after this task is run, and not leaving us enough space to back up the DB, so thats the main problem. I've manually shrunk the tempDB off some code I googled.It's just a small amount of data being selected into the temp table, but many times. Each time the max would be 20 rows, so thats kind of strange. I'll try to look into it more and report back.Are you saying just starting and stopping will get rid of this huge tempdb size? I did it before with some code I found on the net before, but if its as simple as stop start that would be much nicer !:)Thanks! mike123 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 05:17:33
|
| "I've manually shrunk the tempDB off some code I googled"I'll bet you a pint that your Shrink method for TEMPDB is not safe (if you did not explicitly have to start SQL Server in single-user administrator mode then I reckon I'll get my pint!) and could corrupt the database.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Shrinking+TEMPDB (scroll down, it will be highlighted)Stop-starting SQL Server should be enough to shrink TEMPDB [if necessary delete the TEMPDB files whilst SQL Server is stopped]. All other methods imply that something is going wrong at a restart preventing TEMPDB from shrinking as it should."It's just a small amount of data being selected into the temp table"Might be an idea to check that the Sproc is dropping the #TempTable before it exits - shouldn't be necessary, but can't do any harm!Kristen |
|
|
|
|
|
|
|
|
temp tables causing tempdb increase?