tempdb growth SQL 2008 R2

I was performing a test upgrade of a software package and could not figure out why it kept crashing. Later, I discovered that it was an out of disk space issue. However, the DB size is only 5GB and I have 50GB free. What I discovered was it was not the database for the software that was growing it was the tempdb. I tried running SQL Profiler to see what was going on but that shows no activity. The activity Monitor in SQL show some activity but not enough to account for this growth. What tool can I use to find out why the database just keeps growing until all disk space is consumed?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
Is tempdb growing in a consistent manner (i.e. is it something you can repeat, or does it occur at different times)? Is it happening during the upgrade process, or is it something that is happening after the upgrade already been done?

Tempdb is used as a scratchpad for all sorts of things, including queries with CTE or temp tables, index rebuilds etc.
You can run the following query which will show you the page allocation for any running queries:
select * 
from sys.dm_db_session_space_usage spu
join sys.dm_exec_sessions s on s.session_id = spu.session_id
join sys.dm_exec_requests r on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(sql_handle) t 
order by internal_object_alloc_page_count desc

Open in new window

rwheeler23Author Commented:
I have this database on an image and I have tried expanding it three times with similar results. The results are reproducible as it crashes on the exact same statement every time. There is a table called eConnect_Setup_Out. I think someone has made a change to it and now the update program does not know what to do with this change. Here is the actual error message.

The following SQL statement produced an error:
 set nocount on  declare @NAME varchar(100) declare @TEXT varchar(8000) declare taCreateRequesterProcs insensitive cursor for   select DOCTYPE from eConnect_Out_Setup (nolock) where MAIN = 1 and DOCTYPE not like '%GetList'
 open taCreateRequesterProcs fetch next from taCreateRequesterProcs into @NAME while (@@fetch_status <> -1) begin  if (@@fetch_status <> -2)  begin  select @TEXT = 'exec eConnectOutCreate '+ @NAME + ', 0'  exec (@TEXT)  
 select @TEXT = 'exec eConnectOutCreate '+ @NAME + ', 1'  exec (@TEXT)   select @TEXT = 'exec eConnectOutCreate '+ @NAME + ', 2'  exec (@TEXT)  end   fetch next from taCreateRequesterProcs into @NAME end
 deallocate taCreateRequesterProcs  

ERROR [Microsoft][SQL Native Client][SQL Server]Could not allocate space for object 'dbo.#PL' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects
 in the filegroup, adding additional

I am not sure if you can read this but this is the result of your query

session_id      database_id      user_objects_alloc_page_count      user_objects_dealloc_page_count      internal_objects_alloc_page_count      internal_objects_dealloc_page_count      session_id      login_time      host_name      program_name      host_process_id      client_version      client_interface_name      security_id      login_name      nt_domain      nt_user_name      status      context_info      cpu_time      memory_usage      total_scheduled_time      total_elapsed_time      endpoint_id      last_request_start_time      last_request_end_time      reads      writes      logical_reads      is_user_process      text_size      language      date_format      date_first      quoted_identifier      arithabort      ansi_null_dflt_on      ansi_defaults      ansi_warnings      ansi_padding      ansi_nulls      concat_null_yields_null      transaction_isolation_level      lock_timeout      deadlock_priority      row_count      prev_error      original_security_id      original_login_name      last_successful_logon      last_unsuccessful_logon      unsuccessful_logons      group_id      session_id      request_id      start_time      status      command      sql_handle      statement_start_offset      statement_end_offset      plan_handle      database_id      user_id      connection_id      blocking_session_id      wait_type      wait_time      last_wait_type      wait_resource      open_transaction_count      open_resultset_count      transaction_id      context_info      percent_complete      estimated_completion_time      cpu_time      total_elapsed_time      scheduler_id      task_address      reads      writes      logical_reads      text_size      language      date_format      date_first      quoted_identifier      arithabort      ansi_null_dflt_on      ansi_defaults      ansi_warnings      ansi_padding      ansi_nulls      concat_null_yields_null      transaction_isolation_level      lock_timeout      deadlock_priority      row_count      prev_error      nest_level      granted_query_memory      executing_managed_code      group_id      query_hash      query_plan_hash      dbid      objectid      number      encrypted      text
55      2      0      0      8      8      55      2014-08-01 08:37:27.263      GPNYSID      Microsoft SQL Server Management Studio - Query      2680      6      .Net SqlClient Data Provider      0x010500000000000515000000EE0CE1A25D5AB6139F108305F4010000      GPNYSID\Administrator      GPNYSID      Administrator      running      0x      31      2      28      53      2      2014-08-01 08:38:18.797      2014-08-01 08:37:52.543      7      3      82      1      2147483647      us_english      mdy      7      1      1      1      0      1      1      1      1      2      -1      0      1      0      0x010500000000000515000000EE0CE1A25D5AB6139F108305F4010000      GPNYSID\Administrator      NULL      NULL      NULL      1      55      0      2014-08-01 08:38:18.797      running      SELECT      0x020000005FB6563B7C8D80EB691BD40F4DD30DABCDE22227      4      -1      0x060002005FB6563B40A1C1B5000000000000000000000000      2      1      EF428A65-D233-4F99-8E87-7DC50A2D4854      0      NULL      0      SOS_SCHEDULER_YIELD            0      1      4539742867      0x      0      0      0      5      0      0x00000000812EC748      0      0      0      2147483647      us_english      mdy      7      1      1      1      0      1      1      1      1      2      -1      0      1      0      0      2036      0      1      0x668F509F7F561904      0xB5EEA24C240564FD      NULL      NULL      NULL      0        select *   from sys.dm_db_session_space_usage spu  join sys.dm_exec_sessions s on s.session_id = spu.session_id  join sys.dm_exec_requests r on s.session_id = r.session_id  cross apply sys.dm_exec_sql_text(sql_handle) t   order by internal_objects_alloc_page_count desc
Carl TawnSystems and Integration DeveloperCommented:
It's a bit difficult to read the query output in that format :)

Anyway, the error is saying it doesn't have space to allocate a temporary table. Since there is no mention of the temp table in the query you posted, it must be doing it in the "eConnectOutCreate" procedure.

Can you see that procedure and see what it is trying to do? It may be that something is dropping into an infinite loop somewhere, either that or it is doing something with a big chunk of data - but that doesn't sound likely if your database if only 5GB.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rwheeler23Author Commented:
Later today I will compare this eConnect_Out_Setup table to the default version. I bet I am going to find a change that is creating a loop. I will keep you posted.
rwheeler23Author Commented:
As expected someone inserted invalid records into the eConnect table. I remove the records and the upgrade completes.
rwheeler23Author Commented:
Thanks for helping me think this one through.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.