• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1117
  • Last Modified:

SharePoint 2007 -trimaudit log stsadm command does not actually remove audit items

I am currently trying to run the -trimauditlog command on our 2007 SharePoint farm. When I run the command, it says that it completes successfully, but afterwards, when I check SQL, the line items have not been removed. If it makes a difference, I have only tried removing one or two days of logs at a time to not impact the farm too much.

There does not seem to be an associated timer job, but I have forced a run of the timer jobs anyway, and this does not help.

I have not been able to determine a fix for this and the dbo.auditlog table has reached over 5 million items. Any help is appreciated.
  • 3
  • 2
1 Solution
skipper68Application Development ManagerCommented:
1. How much time does your audit log span?  How many months\years?
2. Are you looking to trim it down to a particular size or to a number of months\years?
3. Are you actually querying the auditlog from SQL to determine if particular days are being removed?
4. Are you shrinking the database after you run the command ( DBCC SHRINKDATABASE )?  

Other info.
If you want to have auditing turned on, one common oversight is logging view items.  If you don't log "views" of documents (which includes downloads, opening, and viewing properties) the auditlog will not grow quite as quickly.
You can modify this in the Audit Log Trimming section and specify which events to audit in the Documents and Items and Lists, Libraries, and Sites sections.
Here's a great article for configuring audit log settings
skipper68Application Development ManagerCommented:
Also, there is a CodePlex tool called "SharePoint Audit Log Tool" that will help trim the audit logs if the STSADM way isn't working.
janicet123Author Commented:
Hi and thanks for your response.
To answer your questions -

1. the audit log dates back to June of 2012.
2. We are trying to get the database size down from 56 GB, most of which is the audit log. We are trying to trim it one or two days at a time to determine impact to the farm, but eventually we would like to get it down to 30 days of data.
3. Yes, I query the data after every attempt and the days have not been removed, nor has the amount of items changed.
4. I have not tried shrinking the database after every removal, since I assumed that the items would be removed and not just flagged for removal. Is this incorrect? I am happy to ask our DBAs to shrink the database if necessary.

Unfortunately, we do not have the luxury of determining what items are audited - we have direct instruction from our legal department to audit everything, so turning down the logging is really not an option.

I appreciate the suggestion of the audit log trimming tool. I will check it out, but notoriously have a hard time introducing third party stuff in our environment.

Thanks so much for your help!
skipper68Application Development ManagerCommented:
I understand completely about getting through to a legal team about auditing.  
It's a matter of performance and size.
It may be like beating your head against a brick wall but you may want to get it documented that you offered the suggestion to not track view.  Auditing EVERYTHING is resource intensive.  You should really only turn on events you really need to audit...i.e.
* Does the legal team have a valid business value reason for turning on "View".
* Do you need check-in and check-out logged if you have edit enabled?

As far as controlling audit retention, go to Go to Site actions, Site settings, Site collection Audit settings to activate at the Site Collection level.  I agree that I wouldn't want to keep more than 30 days.

Like you're doing though, definitely delete the older data in chunks otherwise you can lock up your SQL server and effectively crash your SharePoint installation.  There are 536 days since July 1 2012.   Using the powershell below, you can delete audit entries going back incrementally until you can get it down to 30.  At that point you can schedule this to run nightly.

Query your AuditData table to see the number of rows per day to get an idea on how many days to delete at a time safely.

#Powershell Script to delete AuditData table records.

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

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

$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"

Open in new window

janicet123Author Commented:
Thank you so much for this script. It is perfect for what we need! I also appreciate all of your effort in ensuring all the correct questions were addressed! :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now