Friday, June 22, 2012

Trim audit log to Improve Performance in SharePoint

Found SharePoint content databases are growing even when no content was added from user side. I was analyzing SharePoint Database growth, went ahead and generated disk space report from SQL Server Management studio (Right click the database, choose Reports >> Standard Reports >> Disk Usage by Table) which gave me the clear picture: AuditData table was growing rapidly and occupying more disk space.
trim audit log sharepoint 2007
This is because of the auditing feature is turned ON for several Months and Audit logs are stored in the AuditData table of the content database. Auditing feature is great, but it can take so much disk space in a long run and we definitely want to put some cap to preserve system resources, especially database disk space.

SharePoint 2010 provides the interface to specify the number of days to retain audit logs and also provides an option to export the audit logs before truncate.
sharepoint 2010 trim audit logSo, we can adjust the settings to export and clear the audit logs in a interval.

trim audit log sharepoint 2010
trim audit log SharePoint 2010

Delete All Audit Logs Till Yesterday?
So, You noticed the logs are filling the disk space and want to delete all the audit logs, isn't it? No issues, Lets call PowerShell to rescue.
$site = Get-SPSite -Identity http://sharepoint.company.com
#To Get Yesterday
$date = Get-Date
$date = $date.AddDays(-1)
#Delete Audit logs
$site.Audit.DeleteEntries($date)

More info: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spaudit.deleteentries

How to trim audit log in SharePoint 2007?
In MOSS 2007 (after sp2), We have to manually do this trimming with: STSADM -o trimaudtilog.
After executing the above stsadm command, Shrink the database manually to regain the disk space.

stsadm -o trimauditlog –enddate 20120101 –databasename WSS_Content_Func_Finance

These trimming operations could cause blocks in database, which can prevent SharePoint sites from loading.
STSADM -o TrimAuditLog Reference
http://technet.microsoft.com/en-us/library/cc706879%28v=office.12%29

The above method of trimming audit log doesn't provide any way to export the audit log. So here is the workaround, Object model code which exports the audit logs programmatically and deletes.
http://blogs.msdn.com/b/sowmyancs/archive/2008/05/29/create-a-tool-or-interface-to-delete-the-audit-log-from-content-db-of-an-auditing-enabled-sharepoint-site.aspx

Codeplex tool to Trim audit logs for MOSS 2007 : http://sharepointauditlog.codeplex.com/



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

2 comments :

  1. This is a brilliant article, helped us a lot in reducing the size of our database and increasing the site performance.

    ReplyDelete
  2. So its not a document management system if you trim information on who did what when.. might as well use a file share it would be cheaper..

    ReplyDelete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...