The log file alone is close to 500 GB while the database is 80 GB.
This is not a production system so I tried to delete a table which had 780,000 records. this table has 5 individual indexes that are sorted in ascending order, when i tried to delete 1 record with the PK in a clustered index, it got to 20 minutes before I cancelled it.
I left ssms running in "delete From Table" and now the log has exploded to 500GB.I canceled the query and now the database is in recovery. I cant delete anything from this table and its not the IO since i can delete millions from other tables (months ago, this growth is from this one delete). The waits showed a lot of CXPackets and PageLatch_EX
but im trying to understand if there is a setting that its giving me a row versioning overhead. Read commited snapshot isolation is on, but DB snapshot is off.
now the recovery will take hours but what im planning to do it drop the indexes , update the statistics and drop the contraints so that i can try to tuncate but i see myself back here again. Should i delete at batches of 2000 and back up the log to clear it? remember one delete took more than 20 minutes and the only locked transactions are for the pagelatches. I included the query plan for the delete, im worried that somehow the parallel queries have hammered the log file harder that it needs too, any insight would save me loads of time, thanks in advance.
Best Answer
If this is a one off and this isn't production, you could put the database into simple recovery mode, do all the deleting you need to do.
Shrink the log file at this point if you don't want it to be 500GB.
Then put it back into its previous recovery mode.