3

The issue is this: I have a wrapper stored procedure that contains a bunch of other stored procedures. It looks something like this:

Wrapper:

  exec procedure1
  exec procedure2
  exec procedure3

Each of the procedures creates a temp table that are used within it. The issue is that I run out of tempDB space every time I execute the wrapper.

However, while executing each individual stored procedure, it works fine.

My understanding is that the temp tables are dropped after the stored procedure is done. So why is my tempDB getting full?

edit:

This is the error i am getting.

1101 : Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Joe
  • 41,484
  • 20
  • 104
  • 125
Deep grewal
  • 99
  • 2
  • 5
  • 1
    What version of SQL Server? What error (code, message, etc.) information is provided? – Alan Larimer Mar 01 '18 at 03:25
  • @marc_s - eh? It's specifically documented that temp tables created within a stored procedure are dropped when the stored procedure returns – Damien_The_Unbeliever Mar 01 '18 at 09:35
  • @marc_s - [`CREATE TABLE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql#temporary-tables): "A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures..." – Damien_The_Unbeliever Mar 01 '18 at 09:36
  • Does your wrapper and/or the individual stored procedure perform any explicit transaction management? – Damien_The_Unbeliever Mar 01 '18 at 09:36
  • Why do you think that the problem is in any of temp tables? One can run out of tempdb when execute sort/aggregation. And it can be that your procs executed within wrapper just have different execution plans – sepupic Mar 01 '18 at 13:07
  • This is the error i am getting. 1101 : Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. – Deep grewal Mar 01 '18 at 13:18
  • Will the temp table be dropped after each individual SP is finished or after the wrapper is finished? – Deep grewal Mar 01 '18 at 13:20
  • Temp tables are "cached", that is they are not "totaly" dropped, 2 pages of every temp table is saved in cache. But these 2 pages cannot overflow your tempdb. So you can think that after every inner sp is finished, every #tmp table (not ##tmp!!!) is dropped – sepupic Mar 01 '18 at 13:23
  • Why don't you seek your default trace to see if there are sort / hash spills that are always logged in the default trace? – sepupic Mar 01 '18 at 13:29

1 Answers1

1

Running out of disk space in tempdb can cause disruption in the SQL Server environment.There could be various reasons for getting tempdb full like long execution of query, pumping alot of data in tempdb. You need to identify the reason of getting full tempdb. You can go through the following link: https://msdn.microsoft.com/en-us/library/ms176029.aspx This may help you out.

johnwalker10
  • 45
  • 8