0

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.

enter image description here

Improve this question
1
  • 1
    Please format some paragraphs into your question. Walls of text are unlikely to get much help!
    – Mark Sinkinson
    Commented Mar 19, 2018 at 16:38

1 Answer 1

Reset to default
0

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.

Improve this answer
7
  • thats a good point, but my concern is more with defining the underlying issue. its not production but it will be in a couple of months, at that point i wont be able to do this.
    – Berni3mack
    Commented Mar 19, 2018 at 17:05
  • Could you add your delete statement to the question? maybe there's something there to tweak. Also how regular are your log backups running?
    – Ollie
    Commented Mar 19, 2018 at 17:23
  • ok i did, well you got me there, its been about a month, this is inherited by my so obviously im going to change alot. The auto growth is set to percentage in the log so imagine the allocation of that, all in all this is messy and i will start to tackle these things but it blows my mind how one delete can take well over 20 minutes, I'm saying the indexing is the issue. Just not sure what will happen after i succeed in deleting (if i can get to that point)
    – Berni3mack
    Commented Mar 19, 2018 at 17:59
  • 1
    That's probably a big part of the issue for the size. On previous databases I've maintained with operations that cause the log to grow the log backups have been set to every 5 mins so I'd do that first. You might want to do the simple recovery thing and shrink it before that if you can loose what's in the log happily
    – Ollie
    Commented Mar 19, 2018 at 20:12
  • That addresses size. The slowness of deletes could be down to indexes on the table. Either analyse and find an index that can help with efficiency or maybe a code change?
    – Ollie
    Commented Mar 19, 2018 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.