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.
SELECT session_id, command, reads, writes, last_wait_type, wait_type, wait_time, open_resultset_count, open_transaction_count FROM sys.dm_exec_requestsmight help.