InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:03:33 GMT20http://www.sqlservercentral.com/Forums/Topic922805-146-1.aspx[quote][b]saby (6/8/2010)[/b][hr]Hi sam below is the KB for ur issueKB Article Number(s): 2133566[/quote]'Hey , can you please post the actual link for the hotfix?Mon, 06 Jun 2011 01:01:19 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxHi sam below is the KB for ur issueKB Article Number(s): 2133566Tue, 08 Jun 2010 03:33:24 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxSamsql,Grant i had a discussion with MS Team this is fixed in SQL 11 version but not sure about 2008 R2.. They are making an hotfix for it for sqlserver 2005.will let u know once it is launches.Mon, 31 May 2010 03:42:32 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxin SQL Server 2005+, they no longer drop all temp tables from the tempDB. A small cache of 16 temp tables are saved in a special area of cache. This was done to prevent the overhead of constantly allocating new tables for systems that create a lot of temp tables. If an unused table exists in cache, it will use one of them, if not, it creates a new one.This is why you will see temp tables hang around. You can read more about that on Paul Randal's blog: [url=http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx]Misconceptions around TF-1118[/url]If you are experiencing tempDB contention, then you likely do not have your tempDB configured per best practices. Your data files should all be the same size, and you should have multiple data file, somewhere from 1/4 to 1 data file per logical processor. I would suggest starting with 1/4 to 1/2 data file per CPU and increase it if contention persists. You should also separate your tempDB files to a dedicated drive separate from the other databases' files.When you are experiencing heavy blocking in the tempDB, you can use the script on my blog for determining if the contention is on allocation pages (GAM, SGAM, PFS) or data pages: [url=http://www.sqlservercentral.com/blogs/robert_davis/archive/2010/03/05/Breaking-Down-TempDB-Contention.aspx]Breaking Down TempDB Contention[/url]Sat, 22 May 2010 20:22:28 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspx[quote][b]samsql (5/20/2010)[/b][hr]yes saby i m gettin blocking in the tempdb.Grant there are procedure with begin and tran E.g proc ssa(Begin tranexec procedure1-----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...exec procedure2 -----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...exec procedure3-----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...commitrun with diffrent sessions got the blocking while creating a temp tableand one more thing is that the table creation takes 2 secs to create which is really more time.it destroy the table after the 1 procedure get fully exec till commit.[/quote]Yes, that procedure that is calling all the other procedures will hold those temp tables in place until it completes. It doesn't matter that those procedures have transactions of their own, the wrapper proc acts as the main transaction and the temp tables will be held until it commits or rolls back.Thu, 20 May 2010 06:27:43 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxSaby, I'm confused. Where did you get deadlocks in this situation. We're talking about blocking. That's not the same thing as deadlocks.Thu, 20 May 2010 06:26:26 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxyes saby i m gettin blocking in the tempdb.Grant there are procedure with begin and tran E.g proc ssa(Begin tranexec procedure1-----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...exec procedure2 -----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...exec procedure3-----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...commitrun with diffrent sessions got the blocking while creating a temp tableand one more thing is that the table creation takes 2 secs to create which is really more time.it destroy the table after the 1 procedure get fully exec till commit.Thu, 20 May 2010 06:00:23 GMTsamsqlhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxYour process was involved in unresolved deadlock, therefore, the tables were not dropped and blocked the creating table process in tempdb. This might be the bug in sqlserver 2005.create proc proc1asbegin create table #abcd ( i int) drop table #abcd endgo BEGIN TRANgoDECLARE @I INTSET @I = 1WHILE @I < 5000BEGIN EXEC proc1 SET @I = @I + 1ENDgoSelect count(*) from tempdb.sys.tablesgo If you notice the last output, the tempdb now has 5000 temp tables. From a programmer perspective, as soon as a stored procedure finishes, the temp table scope is over and it is destroyed. In SQL2005 we do a deferred drop, but that doesn’t kick in until transaction is over. Because of the begin tran command, the temp tables are not dropped and hence tempdb locks not released. However, these tables are no longer accessible even to the SPID which created it, but are still present in tempdb. Saby DBAThu, 20 May 2010 05:49:25 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxTemp tables last for the length of the process that created them. That wrapper proc is the process, so as long as it's running, you'll keep those temp tables. You can explicitly drop temp tables too.What are the procs doing? It doesn't sound like you're using the temp tables in an efficient manner.Thu, 20 May 2010 05:27:45 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxwaiting for the reply...Jeff can u help in this processThu, 20 May 2010 00:35:04 GMTsamsqlhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxno not using global temp tables ...Tue, 18 May 2010 05:48:53 GMTsamsqlhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxlooks like you are using global temporary tables which may cause blocking:http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.htmlTue, 18 May 2010 03:32:58 GMTblakmkhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxyes but wat i m staying here is there is a procedure in which there are around 30 more procedure which are getting calledEg create procedure abcdef(beginexec ab ---doing some operations creatin 5 temp table doing some updation,insertion etcexec abc ---doing some operations creatin 5 temp table doing some updation,insertion etcexec abe ---doing some operations creatin 5 temp table doing some updation,insertion etcexec abd ---doing some operations creatin 5 temp table doing some updation,insertion etcend)what i know that after the procedure is exec the temp tables gets destroyed from the tempdb.but here in this senario the temp tables are not getting getting destroyed from tempdb.they are getting dropped wen the abcdef procedure gets executed completly.Also wen we r running this procedure from different session we are getting blocking on tempdbwhile creating a temp tables within the proceduresTue, 18 May 2010 00:38:18 GMTsamsqlhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxtemp tables are also created by other processes that perform sorts as part of their operationMon, 17 May 2010 10:03:53 GMTblakmkhttp://www.sqlservercentral.com/Forums/Topic922805-146-1.aspxHi Experts,i have an issue wen i create a temp table it take 1 sec to create a temp table in temp db also wen i check it from the perfmonthe active temp tables shows more the 16 lacs of temp tablethe table name in the tempdb shows as this #table_________________Mon, 17 May 2010 05:12:22 GMTsamsql