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
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

A question that is asked often, is how to generate sequential numbers in InfoPath Forms. The best way to achieve this is to use a SQL database, along with a stored procedure and a web service to connect Forms Services to the DB. The first thing t…
Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

839 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