3

Trying to create a big test DB, I have an SP that I created that uses a predefined list of tables (35 That I'm interested in) and multiplies the data in each one of them.

In other words, A table has data for one store. I insert into this table the same data but with a different store number (which gives me 2 stores). I then call the same SP that multiplies the 2 Stores data to 4 stores, and so on. Over the weekend I started the process of 6 consequent calls of the SP thinking that when I come back Monday my DB would have data for 64 stores. Unfortunately, this wasn't the case...

When I came in Monday, I found the data HD full but the process never crashed and seemed to be suspended. my DB is in simple recovery model, I made space by moving files and by shrinking the TempDB (through the shrink file option in SSMS) and created an extra ndf file with 100GB initial size on an external drive... The process is still running (over 24 hours after making space).

The spid's task state shows suspended with wait type PAGEIOLATCH_EX but with small wait times (below 100ms) which makes me believe that the process is still running.

34 tables out of 35 are decent size and are quick enough to multiply... The process has multiplied the 1 store of data in all tables to 8 stores and is still in the process of completing the multiplication of the data from 8 to 16 stores... the one table it seems to be working on is the biggest table of all...

The actual process of multiplying the data from 8 to 16 stores for this table, is inserting 400Million Records from a select of 400million records and the table has 200 columns all different types.

Am I wasting time waiting? I'm afraid that killing the process will take several days to roll back... Should I just wait it out? Has it become a Phantom process? How can I tell what it's doing?

Any opinion/ideas would be greatly appreciated.

Thank you

  • can you get the spid (from sp_who2) and run DBCC INPUTBUFFER(spid) to see the latest query that it has run, it might tell you which part it is stuck on. – Rich Benner May 9 '17 at 14:25
  • @Rich Benner, unfortunately the DBCC inputbuffer only shows the call of the 6 SPs... although the SP_Who2 does show the DiskIO for this Spid augmente, which suggests that it is advancing – JohnG May 9 '17 at 14:36
  • 3
    I would try sp_whoisactive with @get_full_inner_text = 0 option. – SqlWorldWide May 9 '17 at 14:55
  • @SqlWorldWide I like both solutions provided by HBK and yourself... but have a small preference on the details of your solution... Please put it in the Answers as I want to make this the answer to the question. – JohnG May 9 '17 at 15:31
  • Thx, Doing it now. – SqlWorldWide May 9 '17 at 15:39
5

Download sp_whoisactive stored procedure written by Adam Machanic if you do not have it already. Execute with @get_full_inner_text = 0 option.

From the comment section of the stored procedure:

--If 1, gets the full stored procedure or running batch, when available
--If 0, gets only the actual statement that is currently running in the batch or procedure
@get_full_inner_text = 0

1

Run this query, you should see the actual statements running and the open session activity:

    SELECT   r1.session_id,
         r5.TEXT,
         r1.internal_objects_alloc_page_count + r2.task_internal_objects_alloc_page_count     AS internal_objects_alloc_page_count,
         r1.internal_objects_dealloc_page_count + r2.task_internal_objects_dealloc_page_count AS internal_objects_dealloc_page_count,
         r1.user_objects_alloc_page_count + r2.task_user_objects_alloc_page_count             AS user_objects_alloc_page_count,
         r1.user_objects_dealloc_page_count + r2.task_user_objects_dealloc_page_count         AS user_objects_dealloc_page_count,
         r3.client_net_address,
         r4.host_name,
         r4.program_name,
         r4.last_request_start_time,
         r4.last_request_end_time,
         r4.login_time,
         r4.cpu_time,
         r4.memory_usage,
         r4.reads,
         r4.writes,
         r4.logical_reads,
         r4.status,
         r4.login_name,
         r4.nt_domain,
         r4.nt_user_name,
         (SELECT Count(1)
          FROM   sys.dm_tran_session_transactions t1
          WHERE  t1.session_id = r1.session_id) AS open_transactions
FROM     sys.dm_db_session_space_usage AS r1,
         (SELECT   session_id,
                   Sum(internal_objects_alloc_page_count)   AS task_internal_objects_alloc_page_count,
                   Sum(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count,
                   Sum(user_objects_alloc_page_count)       AS task_user_objects_alloc_page_count,
                   Sum(user_objects_dealloc_page_count)     AS task_user_objects_dealloc_page_count
          FROM     sys.dm_db_task_space_usage
          WHERE    session_id > 50
          GROUP BY session_id) AS r2,
         sys.dm_exec_sessions r4,
         sys.dm_exec_connections r3
         OUTER APPLY sys.Dm_exec_sql_text(most_recent_sql_handle) r5
WHERE    r1.session_id = r2.session_id
         AND r3.most_recent_session_id = r2.session_id
         AND r3.most_recent_session_id = r4.session_id
         AND r1.session_id > 50
ORDER BY internal_objects_alloc_page_count DESC,
         user_objects_alloc_page_count DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.