Solved

tempdb growth SQL 2008 R2

Posted on 2014-07-31
6
309 Views
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?
0
Comment
Question by:rwheeler23
  • 4
  • 2
6 Comments
 
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

0
 

Author Comment

by:rwheeler23
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
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rwheeler23
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.
0
 

Author Comment

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

Author Closing Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now