2

On a test box I wanted to get an idea of how long adding an identity field would take to a table with roughly a half billion rows. The process ran for about 10 hours and we noticed it was getting dangerously low on disk space on the lun for the log.

We addressed the log disk space issue by removing some other items but I'm afraid we might have been too late as it got down to like 10MB.

Is there a way to know immediately if the query began rolling back the alter table statement due to insufficient space? I know a transaction that has had a rollback will show up in sp_who with a status of rollback. I also know a transaction that was purposely killed will have a status of killed/rollback.

But, is there a way to know immediately if a process is being rolled back due to insufficient space before the rollback is complete and the message is returned to SSMS?

Looking at sp_WhoIsActive, sp_Who, sp_Who2 it still shows the original alter table as the command with a status of suspended - I'm concerned that it's actually rolling back however. Is there a way to tell? Am I mistaken and it would show up as rolling back like a killed spid would?

If it fails it fails, but I'd like to know if I'm moving forward or moving backwards.

  • Something like SELECT session_id, command, reads, writes, last_wait_type, wait_type, wait_time, open_resultset_count, open_transaction_count FROM sys.dm_exec_requests might help. – Max Vernon Sep 25 '14 at 21:05
  • @Aaron has a comment on a similar question here: dba.stackexchange.com/questions/51381/… – Max Vernon Sep 25 '14 at 21:06
  • spid: 56, reads: 66280626, writes: 44150147, last_wait_type: PAGEIOLATCH_EX, wait_type: PAGEIOLATCH_EX, wait_time: 568, open_resultset_count: 1, open_transaction_count: 2 – RThomas Sep 25 '14 at 21:11
  • Thanks for the link... that lines up with what I've seen - which reinforces why I think a rollback due to disk space and a rollback due to being killed don't report the same. I listed the results of your recommended query. It's interesting to me that it has 2 open transactions. – RThomas Sep 25 '14 at 21:19
  • 1
    Further, the total reads keeps climbing and the duration reports all the way back to the original statement (16 hours now and over 8 billion reads) wow. – RThomas Sep 25 '14 at 21:34
1

We use sp_whoisactive which has a column called Status. When a query is rolling back you can see status = rollback. It also gives a percentage completion.

We're using SQL 2014, but I'm pretty sure the status was there on 2012 as well

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.