Hello, I am researching how to do data compression on a very large table (2 billion rows). wondering what would happen if for any reason the process is aborted (i.e. kill spid) in the middle. Would it rollback for those already compressed data or the table would become mixed (partially compressed). I did a test on a smaller table (100 Million rows) and killed it in the middle. It was instantaneous. I did not see any rollback effect and I could not tell what is the state of the table (compressed or not). Can anyone shed some light on this? thanks
I've never seen any issues around this or heard any reports about it, but based on experience, it's going to roll back. No way is going to leave the situation with some compressed and some not. In fact, if you really aborted the spid, say by cutting power, I'm fairly certain you'd end up with a corrupt database. As with any other operation of this type, testing is your bestest buddy and only perform the action during low load or down time. And have a good (meaning tested) backup available. A good DBA can't be too paranoid.