none
How do I limit size of Usage ( Usage and Health Data Collection ) database size please?

    Question

  • Hi

    How do I control the size of my usage database ( Usage and Health Data Collection database ) in SP2010 so it never exceeds a certain size?

    Its not clear how you do this - does anyone know? I want to set it so it never exceeds 100 GB in size but logs pretty much everything.

    Thanks in advance,

    Steve

    Tuesday, October 19, 2010 5:24 AM

Answers

  • Hi,

    Check this URL - Log usage data in a different logging database by using Windows PowerShell - http://technet.microsoft.com/en-us/library/ee663480.aspx#section4

    More details on this command -Set-SPUsageApplication - http://technet.microsoft.com/en-us/library/ff607641.aspx

    First create a DB with maxsize, and then set your database using the above method, this should work.

    HTH!


    MCTS - MOSS 2007 Configuring, .NET 2.0 | SharePoint Architect | Evangelist | http://ramakrishnaraja.blogspot.com/
    Tuesday, October 19, 2010 6:17 AM
  • Hi there, SQLGuy777,

    If you take a look:

    1.  In Central Administration's Quick Launch bar and click 'Monitoring'.

    2.  In the Reporting Section, click the 'Configure usage and health data collection'.

    you'll see the settings that control Usage Data Collection (for Usage Analysis), and Health Data Collection. To lighten the performance impact of all this logging, I would suggest you choose only events you need reports on, based on whether or not you're seeing issues on the server(s):

    1.  In the Event Selection area, try to streamline which Events you want to collect data on (for instance, you may not need data on Ratings or Feature Use, but you may need to log on Page Requests and Timer Jobs).

    Depending on the traffic your site sees, Health and Usage can generate lots of data. But due to the performance implications of maintaining the WSS_Logging database, it is recommended the database be placed on a separate server with its own spindle (in some cases, the transaction logs and data should be on two separate spindles).

    Usage and Health Data Collection

    The Usage database can grow very quickly and require significant IOPS.

    For example, in collaborative environments that use out-of-the-box settings, 1 million HTTP requests require 2 GB of storage.

     

    Use one of the following formulas to estimate the amount of IOPS required:

     

    ·         115 × page hits/second

    ·         5 × HTTP requests

    If you must restrict the size of the usage database, we recommend that you start by logging only page requests. You can also restrict the size of the database by setting the default interval of data to be kept to be less than two weeks.

     

    If possible, put the Usage database on its own disk or spindle.

     This article is helpful with regards to your situation, I think!

     

    Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)

    http://technet.microsoft.com/en-us/library/cc298801.aspx

     

    Thanks,

    Tracy

     

    Thursday, December 02, 2010 10:31 PM

All replies

  • Hi,

    Check this URL - Log usage data in a different logging database by using Windows PowerShell - http://technet.microsoft.com/en-us/library/ee663480.aspx#section4

    More details on this command -Set-SPUsageApplication - http://technet.microsoft.com/en-us/library/ff607641.aspx

    First create a DB with maxsize, and then set your database using the above method, this should work.

    HTH!


    MCTS - MOSS 2007 Configuring, .NET 2.0 | SharePoint Architect | Evangelist | http://ramakrishnaraja.blogspot.com/
    Tuesday, October 19, 2010 6:17 AM
  • Hi

    This will stop the database getting too large by physically limiting its growth, but wouldnt the logging app crash one it realises it cant write to the database?

    Is there a way to configure the Usage & Health data collection to change how it behaves please?

    Cheers

     

    SG

     

    Tuesday, October 19, 2010 9:44 PM
  • Hi there, SQLGuy777,

    If you take a look:

    1.  In Central Administration's Quick Launch bar and click 'Monitoring'.

    2.  In the Reporting Section, click the 'Configure usage and health data collection'.

    you'll see the settings that control Usage Data Collection (for Usage Analysis), and Health Data Collection. To lighten the performance impact of all this logging, I would suggest you choose only events you need reports on, based on whether or not you're seeing issues on the server(s):

    1.  In the Event Selection area, try to streamline which Events you want to collect data on (for instance, you may not need data on Ratings or Feature Use, but you may need to log on Page Requests and Timer Jobs).

    Depending on the traffic your site sees, Health and Usage can generate lots of data. But due to the performance implications of maintaining the WSS_Logging database, it is recommended the database be placed on a separate server with its own spindle (in some cases, the transaction logs and data should be on two separate spindles).

    Usage and Health Data Collection

    The Usage database can grow very quickly and require significant IOPS.

    For example, in collaborative environments that use out-of-the-box settings, 1 million HTTP requests require 2 GB of storage.

     

    Use one of the following formulas to estimate the amount of IOPS required:

     

    ·         115 × page hits/second

    ·         5 × HTTP requests

    If you must restrict the size of the usage database, we recommend that you start by logging only page requests. You can also restrict the size of the database by setting the default interval of data to be kept to be less than two weeks.

     

    If possible, put the Usage database on its own disk or spindle.

     This article is helpful with regards to your situation, I think!

     

    Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)

    http://technet.microsoft.com/en-us/library/cc298801.aspx

     

    Thanks,

    Tracy

     

    Thursday, December 02, 2010 10:31 PM
  • Hi there,

    I think what everyone is basically saying is there is no set way in SharePoint to specify the DB size for the usage database. That the tools available through the command line or Central Administration are to control and limit the events that are logged, and to limit the size of the log files themselves.

    What I've always wondered is what *exactly* goes in the WSS_Logging database that isn't in the ULS logs? How exactly is that handled?  When you limit the size of the logs, it will delete the oldest log when the limit is getting reached. Does it delete that info from the DB as well? I've surfaced data from the usage DB using reporting services. And, granted I didn't have a lot of time to mess with it, but it seemed to have less in it that I could use than what was in the ULS (or from the IIS logs in fact).

    Also, I think wanting to record everything, have usage record all events, but keep the database small isn't really realistic (sorry). I have to wonder if maybe you keep the ULS logs ridiculously tiny, if that will help keep your DB small? Of course, that means that your logs will be really short-lived, but it might work for you.

     

    Sorry that there's no easy answer for this. When you bring it up, it does seem like a no brainer request.


    CA Callahan | Author: Mastering Windows SharePoint Service 3.0 | Community Leader | MVP- SharePoint Services
    Thursday, December 30, 2010 7:36 PM