If you have turned on Audit logging in SharePoint 2007, you will wake up some day with a nasty surprise of a bloated SQL server database.  The data is all being stored in the AuditData table in the content database.

Luckily, there is an STSADM command for this, “stsadm -o trimauditlog”
After some research, the general consensus is to not truncate the table directly, although no specific reason is ever given except “Not Supported.”

As I always prefer to use a standard API, I found that you must do this command in very small chunks or else you could lock up your SQL server and effectively crash your SharePoint installation (found out the hard way).

I found 800,000 records to take approximately 15 minutes on a 2 Web Front end 1 Index Server, SQL Cluster type SharePoint farm.  To be safe, I wrote a PowerShell script to take care of these bite sized pieces to run nightly, scheduled as a scheduled task.   Here’s how it works:

  1. Query your AuditData table to see the number of rows per day to get an idea on how many to delete at a time. 
  2. Add the powershell script to your WFE server.
  3. Modify the number of days contant (908 in my case, yes we had a lot).
  4. Create a scheduled task to run the script nightly
  5. Configure Powershell permissions on the server (run Set-ExecutionPolicy RemoteSigned from a Powershell prompt)
     

————————————————————————

#Powershell Script to delete AuditData table records.
#Miles Kan, SurfpointTech.com

$currentDate = Get-Date
#Write-Host $currentDate

#subtract 908 days to sync up to the last day we want to keep in the table
$constantNumberDays = -908

$newDateToDelete = $currentDate.AddDays($constantNumberDays)

$newDateString = ‘{0:yyyyMMdd}’ -f $newDateToDelete
#Write-Host $newDateString

$newSTSADM1 = “stsadm -o trimauditlog -date ”
$newSTSADM2 = ” -databasename WSS_Content”
$newSTSADMFinal = “$newSTSADM1$newDateString$newSTSADM2”

invoke-expression  “$newSTSADMFinal”