2

I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single table) the log grows a lot and the disk is running out of space, and the stored procedure seems to work indefinetly. How can I manage this transaction log?

mustaccio
  • 28,207
  • 24
  • 60
  • 76
maurice
  • 41
  • 1
  • 2

1 Answers1

4

You have two choices:

  1. Break the delete into chunks, as Jonathan explained and as I blogged about here:

  2. Give the transaction log more space to grow to accommodate the log activity (and don't shrink it after every delete; all this does is make the next delete take longer). If you run a delete command that deletes 100K rows, the log has to be able to record all of that activity, regardless of recovery model.

For a much more thorough treatment of the subject, see:

Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624