0

We have an activated stored procedure that writes errors to a table, which I recently learned is 300GB. To get rid of the 300GB I'm going to save out recent data, then truncate/reseed the table during a slow time.

For future maintenance, though, should I look at partitioning or just do periodic deletes? I can imagine a time where I try and SWITCH the partition, it waits using WAIT_AT_LOW_PRIORITY, then kills enough insert threads that the Service Broker thinks there's a poison message issue occurring, and shuts down the queue. (or it hangs, causing contention, because split/merge can't use WAIT_AT_LOW_PRIORITY).

Thanks.

Dale K
  • 25,246
  • 15
  • 42
  • 71
mbourgon
  • 1,286
  • 2
  • 17
  • 35
  • 2
    Of course there are multiple ways to use `WAIT_AT_LOW_PRIORITY` that won't make Service Broker bork itself ([examples here](https://sqlperformance.com/2021/09/sql-performance/refreshing-tables-partition-switching), but basically, `SELF` deems Service Broker more important). Partitioning aside (which is absolutely the least risky way to manage this), you can also consider [deleting in smaller batches](https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes) and/or using [delayed durability](https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014). – Aaron Bertrand Jan 11 '22 at 22:46
  • 2
    Another thing to consider is instead of copying data out of the old table and into another place to save, then deleting it (2X writes), why not just create a new empty table and flip the activation procedure to write to the new table? Now you already have the data you want to save saved, and you didn't have to copy it anywhere, or delete it, or deal with the log impact of both of those activities. – Aaron Bertrand Jan 11 '22 at 22:52
  • Thanks, sir. The reason for the copy was to just save a couple Gb worth of records; I don’t need more. I’ll give partitioning a shot for this. We use partitioning extensively, but I didn’t want to deal with stopping the queue if at all preventable. Appreciate it! – mbourgon Jan 12 '22 at 05:06

0 Answers0