Solved

SQl: Tempdb reserved space not being released

Posted on 2014-01-19
5
1,428 Views
Last Modified: 2014-03-18
Hi All,

We have a stored proc that seem to execute but never deallocate the space it reserves in tempdb.  During the stored proc it creates 7 temp tables each with about 100,000 - 200,00 rows and end of the proc it drops those tables and does a truncate of another table. As this stored proc runs a number of times a day the space increases..and then our monitoring system starts making noise.

Just to make matters more interesting there is a network device between the application servers and the sql server. This device does mutliplexing so there is not a 1:1 relationship between the application server connection pool, and the number of connections on the sql server. So this rules out issuing a hard connection close at the application end, which they are doing anyway.

The tempdb is fairly large, and to date it has not grown. This store proc would need to run for about 8 days with out intervention before the temp started growing. We pre-grew the tempdb.

Any suggestion on this one? I understand that as a stop gap we could check the DMV's and kill off any process matched a predefined profile, but I see that more as a temp work around not as the proper solution.

Cheers
JL
0
Comment
Question by:jaLouden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Assisted Solution

by:magarity
magarity earned 150 total points
ID: 39793214
First, let's make sure the culprit really is the stored proc you suspect.  Here is a query from technet that will show what processes are using how much space in tempdb. Run it when the stored proc is running:
SELECT t1.session_id, t1.request_id, t1.task_alloc,
  t1.task_dealloc, t2.sql_handle, t2.statement_start_offset, 
  t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
    SUM(internal_objects_alloc_page_count) AS task_alloc,
    SUM (internal_objects_dealloc_page_count) AS task_dealloc 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id) AS t1, 
  sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
  AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC

Open in new window

0
 
LVL 2

Author Comment

by:jaLouden
ID: 39793238
Hey Guru,

I can confirm it is  the stored proc. Our monitoring solution is about to show what SPID is using what in tempdb down to the individual file level.

I did however run the command just to be 100% sure..and use the monitoring tool and query match up.

Cheers
JL
0
 
LVL 35

Accepted Solution

by:
David Todd earned 150 total points
ID: 39793366
Hi,

What I'd do/What I've done:
With a couple of big ETL procedures, I've searched from the end of the file and found the last reference to temp file abc. After that, I've put an explicit if exits drop.

I did get a performance improvement in doing so, and not just letting the procedure end and delete the temp objects automatically.

HTH
  David

PS Template code for dropping a temp table
if object_id( N'tempdb..<temptable_name, sysname, #t>', N'U' ) is not null 
	drop table <temptable_name, sysname, #t>;

Open in new window

0
 
LVL 2

Author Comment

by:jaLouden
ID: 39793369
Hey David,

Will give that a go, looks good. Stay tuned.

Cheers
JL
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39793378
ah

Some of my last comment should be

... explicit if exists drop.

Regards
  David
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 62
MSSQL Query for Selecting the SUM of a Specific Group 2 34
What type of testing am I doing? 4 75
Server 2012 r2 and SQL 2014 6 33
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question