Solved

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

Posted on 2013-11-18
5
963 Views
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.
0
Comment
Question by:janicet123
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:skipper68
Comment Utility
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
0
 
LVL 9

Expert Comment

by:skipper68
Comment Utility
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.
0
 
LVL 7

Author Comment

by:janicet123
Comment Utility
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!
0
 
LVL 9

Accepted Solution

by:
skipper68 earned 500 total points
Comment Utility
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

0
 
LVL 7

Author Closing Comment

by:janicet123
Comment Utility
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! :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now