Go Premium for a chance to win a PS4. Enter to Win


tempdb growth SQL 2008 R2

Posted on 2014-07-31
Medium Priority
Last Modified: 2014-08-05
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?
Question by:rwheeler23
  • 4
  • 2
LVL 52

Expert Comment

by:Carl Tawn
ID: 40233617
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


Author Comment

ID: 40234072
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
LVL 52

Accepted Solution

Carl Tawn earned 2000 total points
ID: 40234118
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.
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal


Author Comment

ID: 40234126
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.

Author Comment

ID: 40242837
As expected someone inserted invalid records into the eConnect table. I remove the records and the upgrade completes.

Author Closing Comment

ID: 40242838
Thanks for helping me think this one through.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

824 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