Purging MOSS DB, auditdata table in SQL

Hi, There is MOSS database (ContentDB) which contains auditdata table of 150GB size and one million rows. This table has a column with datatype ntext. A delete command is fired from sharepoint side (stsadm command) which deletes data from table for one day. When delete is happening, log file grows to 240GB and finally fails as we dont have any more space for log file to grow. It seems sharepoint tables should not be deleted directly from SQL side. I tried changing recovery mode to bulk logged but no help as delete is not a minimally logged operation. Also truncate log doesnot work as whole delete is one huge transaction.

Why deleting just one day's data creating 240+GB of log?Is there anyway to handle this?

Thanks

asked Nov 11 '10 at 06:31 PM in Default

Sonali
11 ● 1 ● 1 ● 1

(comments are locked)

1 answer:

If you delete data from a table and DB is in Full recovery or bulk mode, then every deleted record is logged to a transaction log. If you need to delete large amount of data and don't have enought log space, you have several possibilities.

Delete data in batches and backup log between batches
Try backup the log before deleting and if there is still not enought space, add some space to log.
If you want to delete all the data, then switch to bulk logged and truncate table
Switch to simple mode and delete data. Then go back to full recovery mode.