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

Posted on 2013-11-18
Last Modified: 2013-12-02
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.
Question by:janicet123
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

ID: 39659350
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

Expert Comment

ID: 39659368
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.

Author Comment

ID: 39659771
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!

Accepted Solution

skipper68 earned 500 total points
ID: 39659876
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


Author Closing Comment

ID: 39690160
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! :)

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question