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

Posted on 2013-11-18
Medium Priority
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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I used to be SharePoint evangelist in our company, so my Outlook always full of questions about how to do this, or where I can find that. One day I found such an email with the following question: "how to attach 3-State workflow (one of the workflow…
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 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