patternsqlMinor
tsql Process running forever... what is it doing
Viewed 0 times
tsqlprocesswhatdoingrunningforever
Problem
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
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
Solution
Download
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
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
Context
StackExchange Database Administrators Q#173096, answer score: 5
Revisions (0)
No revisions yet.