How should I setup a table of temporary/transactional items that I only need for a few days or weeks? We have created a table in Sql Server 2012 that we use to store temporary records of transactions with our integration partners. These records can be text, XML or BLOBs. They're not actually used for anything by our software but are for debugging, recovery and investigation purposes. After a certain age they are deleted.

As volume has increased recently, this table has grown immensely in size (50GB at the moment, individual rows are as much as 25MB). This is not a problem in itself. However, the deletion scripts that clear out old records are no longer running to completion in reasonable amounts of time.

Is there a better way to store and clean up this data? Would partitioning be useful for this? Is there some other technology that would help manage a rolling date range of large data?
First, check execution plan for DELETE - may be you'll get additional ideas.

Second, I suggest you to choose from two standard patterns for such tables.


Use DELETE TOP(N) to remove data. N should be chosen
experimentally.
Use set of tables (e.g. 10 tables) to store data by day or week and "rotate" it periodically. In this case you can TRUNCATE "old" tables which is much faster than DELETE.