Friday, April 26, 2013

SharePoint 2013/2016: SQL Partition Exceeded Max Bytes

We noticed on one of our new application servers that the event log had multiple errors stating:

Table SqlIoData_Partition18 has 442900480 bytes that has exceeded the max bytes 442857142


From the Task Category, notice that this is coming from the Usage Infrastructure. The root cause was that we were collecting too much usage data and most of it was probably unneccessary.

To throttle the usage collection, from Central Administration click on Monitoring from the quick launch:



On the Monitoring page, click on Configure usage and health data collection:

Notice the important info here..."..but also uses system resourcs and can impact performance and disk usage". Crap. I don't need all that. Simply uncheck items you don't care about and only have several items checked:


If you are like me you probably don't know what half these things are anyway. Here are the events that I kept checked:  (click on the image for a larger view)

The text on the page gives us a little helpful hint. Enable the events you need to investigate issues and then disable them after the investigation is complete.

No more max bytes errors in the event log anymore!

UPDATE 5/1/2013: I spoke too soon. Apparently leaving the SQL IO Usage produces a large amount of usage data. I had more max bytes errors this morning. This is with just a handful of us on the new production servers. I unchecked SQL IO Usage and SQL Latency Usage. Eventually I'll probably be down to just Page Requests being checked.

UPDATE 9/18/2013: After applying the SharePoint 2013 updates and enabling Usage for Page Requests, I still encountered this error. Seems like the root cause and potential workarounds are explained here: http://blogs.msdn.com/b/sharepoint_strategery/archive/2012/04/16/usage-file-and-web-analytics-reports-with-blind-spots.aspx

8 comments:

  1. Good catch. This was very helpful

    ReplyDelete
  2. Steve, did you ever figure out another method besides removing usage data to include in the log? From your update it sounds like the events returned even after reducing the items.

    ReplyDelete
    Replies
    1. No not yet. We went live with SharePoint 2013 so now it is our Production environment. I believe usage is still off. I applied the March PU, April CU, June CU, and August CU. Maybe one of these fixed it but I haven't turned it back on to see.

      Delete
    2. After applying the SharePoint 2013 updates and enabling Usage for Page Requests, I still encountered this error.

      Seems like the root cause and potential workarounds are explained here: http://blogs.msdn.com/b/sharepoint_strategery/archive/2012/04/16/usage-file-and-web-analytics-reports-with-blind-spots.aspx

      Delete
  3. .... after turned off Logging in Configure usage and health data collection, I still see this error "Table RequestUsage_Partition7 has 442966016 bytes that has exceeded the max bytes 442857142"
    who can help me?

    ReplyDelete
    Replies
    1. Follow the steps in the post below. It helped me resolve the issue.
      http://blogs.msdn.com/b/sharepoint_strategery/archive/2012/04/16/usage-file-and-web-analytics-reports-with-blind-spots.aspx

      Delete
  4. Great article, much appreciated :-)

    ReplyDelete
  5. Hi there! glad to drop by your page and found these very interesting and informative stuff. Thanks for sharing, keep it up!

    ReplyDelete

Matched Content