What is the WSS_Logging Database?

 

What is the WSS_Logging database?

I was recently at the SharePoint Saturday in Bend and one of the people that attended my session asked a question afterwards that I answered, but I don’t think I gave the response enough justice. The question I was asked was “What is the WSS_Logging database?” After talking with him the underlying problem presented itself. He was having problems because his WSS_Logging was getting rather large. So I explained to him what I thought the database was used for. In general terms it is used for gathering ULS data for the farm. Which if you think about it, this of itself is pretty cool. If you have a 10 server farm you have one location to check for any errors. But this database does so much more. Not only does it aggregate the ULS data it also contains the server event log information as well. The health analyzer tool in Central Admin also uses this database to warn you of problems. In SharePoint 2010 the default name for this database is WSS_Logging in SharePoint 2013 the default is SharePoint_Logging. Other than that the database and information is pretty much identical. In SharePoint 2013 a lot of the usage reports are now part of the Search Databases however.

 

How does this database get its data?

This database gets its data from SharePoint Timer Jobs that run on all the servers in the farm. The data for each type of item is “Partitioned” into tables for the day in question. These partition tables are generated based of the log providers that have been setup. You can control what data gets into this database and on what schedule it gets added from Central Admin -> Monitoring – Configure Usage and Health Data Collection. From here collection can be turned on and off and you can control what items are pulled into the tables.

HealthCollectionTypes

As you can see there are plenty of options that you can select and they are all selected by default. Further down on the screen there is an option that will you to configure the schedule of the data collection. The link is called “Log Collection Schedule”. If you click on this link you will be taken to the two jobs that are used for logging. They are:

  • Microsoft SharePoint Foundation Usage Data Import – Imports the data into the logging database, defaults to running every 30 minutes.
  • Microsoft SharePoint Foundation Usage Data Processing- Handles the processing to move the data into the correct buckets, runs daily.

For each of the events that you select to log you will get tables that correspond. Included below is a snapshot of the tables created for ULS Logging.

WSSLoggingULS

These tables continue through 31. This means you could have data for the last 31 days. The processing job makes sure that daily the events that are tracked are in the correct table for the day.

 

What can I do with this Database?

Microsoft’s TechNet article on this database has an interesting note, “the logging database is the only SharePoint 2013 database for which you can customize reports by directly changing the database.” (http://technet.microsoft.com/en-us/library/jj715694(v=office.15).aspx). Most of the time we are told never touch the SharePoint databases but this is one exception to the rule. Now we are still limited in what we can do with it but at least we won’t get our hands slapped if we look at it. To change the reports we can create SQL views if we want a different view of the report data that is already logged or we can create a custom log provider that will allow us to add new data.

 

Now What?

Now for the million dollar question, how do we control how big this database has become. The simplest option would be to limit the event types that are tracked. This could be done in two ways, by removing the event type all together or going into the Configure Diagnostic Logging screen and changing the logging level. ULS logging can take up a lot of space on the file system and if you multiply this space by the number of servers that are in the farm you can see how this can get out of control really quickly. Limiting what is logged will not immediately fix your database size since the old data will be removed automatically after it is too old.

Another option is to look at which table it is in the database that is taking up the space and you get adjust how long the data is retained in the database. The tables allows us to retain up to 31 days worth of data but the default is 14. To check what you retention level is run the following powershell script: Get-SPUsageDefinition.

If you run this script on a default install that has logging enabled will give you the following output.

UsageRetention

From PowerShell you can change the Retention Period for each individual Event Type by using the Set-SPUsageDefinition commandlet. This commandlet lets us set the retention days and whether an event type is enabled or not. So if we wanted to set the retention period for Timer Jobs to one week instead of two we can run Set-SPUsageDeffinition -Identity “Timer Jobs” -DaysRetained 7. After this is done go to both of the timer jobs mentioned above and run them. This will flush the data that is older than the retention period out of the database. So in this scenario any timer job data older than 7 days will be removed.

14 thoughts on “What is the WSS_Logging Database?”

  1. Hi,

    Great post, however the default database name in SharePoint 2013 is also WSS_Logging and not SharePoint_logging.

    Regards,

    Paul

    1. If you are doing/did an in-place upgrade from SharePoint 2010 this would be true but Microsoft’s documentation says otherwise, some places included below. Now that being said I have verified on different clients and installs that I have done myself and what I am seeing is that upgrades definitely have the WSS_Logging but new installs I am seeing a different value all together, WSS_UsageApplication. So since the documentation states it is SharePoint_Logging I’ll leave it as such in the post but reader be aware that it may have a different name. To verify what it is called on your Farm, go to Central Admin – Monitoring – Configure usage and health data collection. Towards the bottom of this page there is a section called Logging Database Server. In this section it will tell you which server the database is on and what the database name is.

      Usage database
      Category Description
      Default database name when installed by using the SharePoint Products Configuration Wizard
      SharePoint_Logging
      (https://technet.microsoft.com/en-us/library/cc678868.aspx)

      Note:
      The default name of the database is SharePoint_Logging. You can change the default name and the SharePoint database server location by using Windows PowerShell. For more information about the logging database name and database server location, see Configure usage and health data collection in SharePoint 2013.
      (https://technet.microsoft.com/en-us/library/jj715694.aspx)

  2. Hi,
    is there any such feature supported by SP-online like capturing logs into wss_logging db.. if not, is there any alternative for this in sharepoint online? How can we get logging information in sharepoint online?

    Regards,
    Suresh.

    1. Sadly there is not currently a setup to get logging information about of SharePoint Online. I hope it comes at some point in time but with the fact that SPO is shared the log files for many clients would be mixed together and it would be tricky trying to separate everything. So I don’t know that it will happen.

        1. That is correct. You can use Azure Application insight for SPO since you won’t have access to this database. You could also use Azure Application insight with on-prem SharePoint as well.

  3. Hello,

    My WSS_Usage application database size is around 80GB.
    I have reduced the retention period to 3 days and run the timer job, still the database size is around 80GB.
    I have disabled the logging now so that my database would not grow any further.
    In this case, will my database refreshes the data stored and free space once the log days is more than 31 days.
    As per few forum suggestions, I am in a process of deleting the service application and recreating a new one to free space.
    Please advice if there are any other methods to free space without deleting and re creating the service application.

    Thanks

    1. I am not sure on that one. I think there is the possibility that it could take some for the timer jobs to run.

  4. Hi,

    I have Sharepoint fodundation 2010 site in which Wss_logging database got corrupted ,due to this our backup agent unable to execute the backup plan as scheduled.

    I have checked the database through SQL management studio, I got to know few pages are corrupted due to unknown reasons.

    Now, I am unable to take backup of that database.

    Please help is there any way to fixed that issue?

    Thanks
    Amit Verma

    1. I would say then, since the logging database is corrupted, if it was me I would delete and recreate the logging service so you get a new database.

  5. Hi,

    i have a specific sharepoint 2016 site which is not giving usage reports. Its displays ‘0’ every where.

    All the other site collections in the farm generate the report properly.

    Any idea what is causing this.

  6. If you guys want to set all the UsageDefinition to 1 day you can use the script below

    #add SharePoint cmdlets (if not already loaded)
    if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
    {
    Add-PsSnapin Microsoft.SharePoint.PowerShell
    }

    $usageDefin = Get-SPUsageDefinition
    for($i = 0; $i -lt $usageDefin.Count; $i++){
    Write-Host $usageDefin[$i].Name
    Set-SPUsageDefinition -Identity $usageDefin[$i].Name -DaysRetained 1
    }

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.