none
Shrinking the WSS_UsageApplication database RRS feed

  • Question

  • Hello Team

    As we have SharePoint server 2013 on-premises and running out of space on the drive due to my database "WSS_UsageApplication" increased to 115 GB. I tried to shrink the database as well as used http://blogs.msdn.com/b/manhar/archive/2012/04/17/how-to-reduce-the-size-of-logging-database-or-how-to-purge-the-old-data-from-logging-database.aspx but still unable to reduce the space for "WSS_UsageApplication"

    Page Request and Task Use for both i did the retention policy to 3 days instead of 14 days and run both jobs bit still database growing. Its increasing in gigantic. Please help


    Thanks Ranveer Katiyar

    Thursday, June 26, 2014 5:37 PM

Answers

  • Hi,

    According to your post, my understanding is that you wanted to reduce the size of “WSS_UsageApplication” database in SharePoint 2013.

    So, I recommend that you can write the following SQL Script to clean out the tables on your dev box WSS_Logging database.

     

    DECLARE @TableName AS VARCHAR(MAX)
    
    DECLARE table_cursor CURSOR
    
    FOR                                                                       
    
    SELECT TABLE_NAME 
    
    FROM INFORMATION_SCHEMA.TABLES 
    
    WHERE TABLE_TYPE = 'BASE TABLE'
    
    AND TABLE_NAME LIKE '%_Partition%'
    
    OPEN table_cursor
    
    FETCH NEXT FROM table_cursor INTO @TableName
    
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    
    DECLARE @SQLText AS NVARCHAR(4000)
    
    SET @SQLText = 'TRUNCATE TABLE ' + @TableName
    
    EXEC sp_executeSQL @SQLText
    
    FETCH NEXT FROM table_cursor INTO @TableName 
    
    END
    
    CLOSE table_cursor
    
    DEALLOCATE table_cursor
    

    For more information, you can refer to the following article:

    http://blog.walisystemsinc.com/2012/11/truncate-wsslogging-db.html

    What’s more, there is an article about how to shrink SQL / SharePoint database / log files, you can have a look at it.

    http://www.satheesh.net/2011/04/05/how-to-shrink-sql-sharepoint-database-log-files/

    Thanks,

    Jason


    Jason Guo
    TechNet Community Support

    Friday, June 27, 2014 11:51 AM

All replies

  • You need to reduce the retention for all categories not for one(page) as per your mentioned article.

    try this one limit all usage definitions to 3.

    to check: 

    Get-SPUsageDefinition

    To limit:

    $defs = Get-SPUsageDefinition
    
    Foreach($def in $defs)
    
    {
    
    Set-SPUsageDefinition –Identity $def.Name –DaysRetained 3
    
    } 

    Now run both timer jobs, hopefully you see some free space in db. time to shrink it now.

    http://adammcewen.wordpress.com/2013/02/01/shrinking-the-wss_logging-database/


    Please remember to mark your question as answered &Vote helpful,if this solves/helps your problem. ****************************************************************************************** Thanks -WS MCITP(SharePoint 2010, 2013) Blog: http://wscheema.com/blog

    Friday, June 27, 2014 4:20 AM
    Moderator
  • Hi I did it for all but getting issues in Tenant Logging. When i run it for Tenant Logging its give us error-

    PS C:\Users> Set-SPUsageDefinition -Identity "Tenant logging" -DaysRetained 3
    Set-SPUsageDefinition : An update conflict has occurred, and you must re-try this action. The object SPTenantLogUsageDefinition was updated by domain\User, in the OWSTIMER (4776) process, on machine SystemName.  View the tracing log for more information about the conflict.
    At line:1 char:1
    + Set-SPUsageDefinition -Identity "Tenant logging" -DaysRetained 3
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidData: (Microsoft.Share...UsageDefinition:
       SPCmdletSetUsageDefinition) [Set-SPUsageDefinition], SPUpdatedConcurrencyE
      xception
        + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletSetUsage
       Definition


    Thanks Ranveer Katiyar

    Friday, June 27, 2014 5:46 AM
  • Stupid question but this is the mdf file that's growing, not the ldf?
    Friday, June 27, 2014 6:21 AM
  • Hi,

    According to your post, my understanding is that you wanted to reduce the size of “WSS_UsageApplication” database in SharePoint 2013.

    So, I recommend that you can write the following SQL Script to clean out the tables on your dev box WSS_Logging database.

     

    DECLARE @TableName AS VARCHAR(MAX)
    
    DECLARE table_cursor CURSOR
    
    FOR                                                                       
    
    SELECT TABLE_NAME 
    
    FROM INFORMATION_SCHEMA.TABLES 
    
    WHERE TABLE_TYPE = 'BASE TABLE'
    
    AND TABLE_NAME LIKE '%_Partition%'
    
    OPEN table_cursor
    
    FETCH NEXT FROM table_cursor INTO @TableName
    
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    
    DECLARE @SQLText AS NVARCHAR(4000)
    
    SET @SQLText = 'TRUNCATE TABLE ' + @TableName
    
    EXEC sp_executeSQL @SQLText
    
    FETCH NEXT FROM table_cursor INTO @TableName 
    
    END
    
    CLOSE table_cursor
    
    DEALLOCATE table_cursor
    

    For more information, you can refer to the following article:

    http://blog.walisystemsinc.com/2012/11/truncate-wsslogging-db.html

    What’s more, there is an article about how to shrink SQL / SharePoint database / log files, you can have a look at it.

    http://www.satheesh.net/2011/04/05/how-to-shrink-sql-sharepoint-database-log-files/

    Thanks,

    Jason


    Jason Guo
    TechNet Community Support

    Friday, June 27, 2014 11:51 AM
  • I ran the above script on Friday after that i got the more free space and its again increased to 194 GB today. Can you please tell me what is writing in the WSS_UsageApplication. We stopped the crawl to check out DB size, its still increasing frequently.

    Thanks Ranveer Katiyar

    Monday, July 7, 2014 1:00 PM
  • check this post, may help you

    http://sharepoint.stackexchange.com/questions/31778/wss-logging-database-size


    Please remember to mark your question as answered &Vote helpful,if this solves/helps your problem. ****************************************************************************************** Thanks -WS MCITP(SharePoint 2010, 2013) Blog: http://wscheema.com/blog

    Monday, July 7, 2014 5:18 PM
    Moderator