SharePoint reduce growing disk space by reducing WSS_LOGGING dbase size

WSS logging database grows very fast and it cause the storage problem most of the time in sharepoint server 2010.

To reduce the size of the logging database or to purge the old data from the logging database we can perform these steps.

Execute the following script:By Default the system retains 14 days of Usage data in this database. By following PowerShell command we can check and change the value of retention days of this database.

Add-PsSnapin Microsoft.SharePoint.PowerShellWrite-Host “Data retention policy, which is set to 14 days by default.Going to set it to 3 days.”

   1:  Add-PsSnapin Microsoft.SharePoint.PowerShell
   2:  Write-Host "Data retention policy, which is set to 14 days by default.Going to set it to 3 days."
   3:  Get-SPUsageDefinition
   4:  $defs = Get-SPUsageDefinition
   5:  
   6:  Foreach($def in $defs)
   7:  {
   8:  Set-SPUsageDefinition –Identity $def.Name –DaysRetained 3
   9:  }

write-host “Now, open Central Admin and choose, Monitoring > Configure usage and health data collection > Log Collection Schedule>.”

Write-host “Execute the two Timer jobs:”
Write-host “1)Microsoft SharePoint Foundation Usage Data Import”
Write-host “2)Microsoft SharePoint Foundation Usage Data Processing”
Write-host “The SQL logging database will now contain some free space”
Write-host “which your SQL DBA can ‘free-up’ within SQL Management Studio ”
write-host “or running the ‘DBCC ShrinkFile’ T-SQL command.”
Remove-PsSnapin Microsoft.SharePoint.PowerShell

After that we need to run the two timer jobs to clean the old data ‘Microsoft SharePoint Foundation Usage Data Import’ and ‘Microsoft SharePoint Foundation Usage Data Processing’.Go to Sharepoint Central Administration -> Monitoring -> Configure Usage and health data collection-> Log Collection Schedule.

And it will take you to the timer jobs.

Now Click on both the Job Definitions one by one and hit ‘Run Now’ to run the timer jobs

Once the timer jobs is run you can check and confirm database has released the space.
You can shrink the database after this by using ‘DBCC ShrinkFile’ T-SQL command

Original Source: Binary Republik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: