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 |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2012-10-25 : 10:43:22
|
| My approach to Temp Tables in Stored Procedures has always been that there is no need to explicitly drop these and SQL Server will do this automatically when the execution of the Stored Procedure has completed.But our vendor has called this into question. They noticed a number of temp tables remaining with names like, #0034AE3E, even though nothing was running. I had noticed these before but made nothing of it. I just read that even though SQL Server drops these for you, these may remain in temp db just in case the structure is needed again. That seems a bit confusing to me.I had a look at our tempdb, it is at 9 gig even though nothing was running. I read that SQL Server may need to claim space for TempDB if it needs it and won't Release this space by default since it could need it again later. Which made sense. So I ran: DBCC SHRINKDATABASE (tempdb, TRUNCATEONLY) but the Database size remaind at 9 gig, not sure why.Hopefully someone can enlighten me:1. Is it a best practice to drop temp tables in a Stored Procedure or is that unnessessary?2. Will dropping temp tables explicitly in a Stored Procedure lead to minimizing the space usage of Temp DB?3. Why does my SHRINKDATABASE statement not free up more space?Any answers or pointing me in the right direction would help. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-10-25 : 10:57:28
|
| The temp tables are probably there because someone has been testing, created them manually and hasn't dropped their connection.That can also cause problems if they try to create an SP on the same connection and run it.1. Not necessary but I usually do as it's handy to havethe statements for testing2. Maybe - if they are dropped early in the SP.3. It's not a good idea to shrink a database unless you know that the space is not going to be used again. In your situation I would bounce the server which will recreate tempdb and also close any connections.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-10-28 : 18:16:31
|
quote:
Originally posted by nigelrivett
The temp tables are probably there because someone has been testing, created them manually and hasn't dropped their connection.That can also cause problems if they try to create an SP on the same connection and run it.
I don't think this is the case but I will drop the connections to check.quote:
1. Not necessary but I usually do as it's handy to havethe statements for testing
Thankyou.quote:
2. Maybe - if they are dropped early in the SP.3. It's not a good idea to shrink a database unless you know that the space is not going to be used again. In your situation I would bounce the server which will recreate tempdb and also close any connections.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
|
|
|
Temp DB - questions, dropping temp tables and size