none
Trimming Audit Log - performance hit and transaction log explosion

    Pregunta

  • I've recently discovered the AuditLog table debacle.. my AuditData table is by far the largest table in my entire Content Database, and of course I'd like to shrink it down significantly.

    I'm running Sharepoint 2007 SP1 + Infrastructure update, but no further updates beyond that.  (I have a project to upgrade it, but exhaustive testing is required before I can proceed with that.)  

    I've researched the 'stsadm -o trimauditlog' command, and have attempted to run it.  However, the results are horrendous.  The moment that I execute it, it practically kills IIS on my web frontend server (I have to restart IIS completely in order to recover), and my wss_content_log.ldf file explodes in size.  Even after 20 minutes of letting it run, my log file grows by hundreds of gigs (which is unacceptable, I don't have that much room on my drive for that) the AuditLog table only shrinks by oh, 10 megs.   It's like watching an outlook PST file compress itself.  I think I'd rather stare at the sun. 

    From what I've read in forums and other sources, others have ran into this too but I haven't seen anybody say anything like, "Yeah, this is fixed in xxx update" or "Well, I found another way to handle this."  My company wants to utilize these auditing features but only keep the data for 90 days.  Currently, I have about 2 years of data and the AuditLog table is taking up almost 17 gigs.  If it requires 20 minutes to shrink it by 10 megs with a 10,000:1 transaction log growth ratio, then me & this sharepoint service are gonna go outside and take care of this like men.  

    And I fight dirty.

    Any thoughts?  Does this behave similarly for everyone?  Or do I get some awesome fix in a later upgrade, like SP2?  Are there any tools that handle this better?  

    (fwiw, I found a tool, http://sharepointauditlog.codeplex.com/ that touts an improvement, but it has the same results as by doing it by hand.)

    Do I have any choice other than claiming "Sharepoint Auditing is unscalable and practically unusable in a real world scenario?"


    Gabriel Matthews Systems Engineer - QServices Co.
    viernes, 23 de julio de 2010 19:51

Respuestas

  • Yeah, I'm not surprised at no responses.. :)  I've been sleeping on this, and after talking to my boss (who has much more greater DB kungfu than I), we settled on the following:  Using stsadm -o trimauditlog to trim the log in small increments.  So, if my table is filled with data back to 20080601, then I run it as 20080701, then 20080801, etc.  

    It still takes foreve, and apparently it still locks the table to the point that the site does hang until it's done.  But I'm planning on scripting it to go one day at a time, with like a 60 second sleep inbetween.  Not fancy, but at least it shouldn't take the site down for 3-6 hours while I run a huge trim against it.  Plus, this way I can backup & truncate my transaction logs during the process.

    I'll post my batch script when I'm done writing it, in case others look for this same issue and find the post useful.

    Later!


    Gabriel Matthews Systems Engineer - QServices Co.
    • Marcado como respuesta Lily Wu viernes, 06 de agosto de 2010 1:00
    domingo, 25 de julio de 2010 21:13

Todas las respuestas

  • Yeah, I'm not surprised at no responses.. :)  I've been sleeping on this, and after talking to my boss (who has much more greater DB kungfu than I), we settled on the following:  Using stsadm -o trimauditlog to trim the log in small increments.  So, if my table is filled with data back to 20080601, then I run it as 20080701, then 20080801, etc.  

    It still takes foreve, and apparently it still locks the table to the point that the site does hang until it's done.  But I'm planning on scripting it to go one day at a time, with like a 60 second sleep inbetween.  Not fancy, but at least it shouldn't take the site down for 3-6 hours while I run a huge trim against it.  Plus, this way I can backup & truncate my transaction logs during the process.

    I'll post my batch script when I'm done writing it, in case others look for this same issue and find the post useful.

    Later!


    Gabriel Matthews Systems Engineer - QServices Co.
    • Marcado como respuesta Lily Wu viernes, 06 de agosto de 2010 1:00
    domingo, 25 de julio de 2010 21:13
  • Hi Gabriel,

    We shrink audit tables daily using trimaudit in batch file that strips them out to below 90 days of age - runs out of hours to reduce impact.  I think the issue you have hit is a typical one in that the audit data table grew very large, so takes some cleaning out.  So once you have done your initial cleanup, you need to set this up as a daily activity that only takes a very short while to actually run.

    You are on the right track - take the pain until you have it stripped down, there really isn't any other "clean" option.

    Regards

    John Timney

    lunes, 26 de julio de 2010 9:48
  • Hello

    I am new to this topic, and even in our company, decided to truncate the audit data table and it has almost 120GB of data right from the day SP 2007 was installed on our servers. 

    Instead of the stsadm command, can we trucate the table from the backend, Database.

    Please suggest.

    Thanks

    Venkat.


    jueves, 23 de febrero de 2012 17:04
  • Cont..

    Using the trucate sql query on the table and connecting to the content database.

    Thanks

    Venkat.

    jueves, 23 de febrero de 2012 17:30
  • Hello all....

    I have truncated the Audit data table using the stsadm command and also from the backend.....deleting the table data with the sql query/ using the truncate option for the table(even though Microsoft does not give support)..... The application is working and it did not take the sites/site collection down but the sites come up slow.....

    Thanks

    Venkat..... 

    martes, 10 de abril de 2012 23:29