SharePoint Server 2010 introduced Web Analytics. As part of the reporting component for Web Analytics, we create two databases, a reporting and staging database. The Web Analytics service is responsible for pushing data into these databases. The data is stored in the reporting database in a format that might look a little puzzling to some folks. Each week, we create an NDF file, which is a table partition. After a period of time, you’re going to see a number of NDF files, in addition to your MDF and LDF on your SQL Server. Again, these are table partitions, not additional databases. You may notice that they’re taking up an significant amount of space and want to clean them up. Please do not delete these NDFs. There is a way to reclaim the space. As part of the Web Analytics, there is a database maintenance job, that purges data from the staging and reporting databases. It removes the data, once it’s passed the specified retention period. This job runs daily at 4:00 AM. This is no way to force this to run, or to change the time. You can see entries in your ULS logs that correspond to this activity. They may look like the following entries. I’ve listed the ID and the wording below.

  • c961 – Purging the old data in the databases.
  • c96m – Waiting for data purging threads to complete.
  • c96n – Failed to purge some old data due to it has taken more than {0} minutes configured.
  • c96o – Purged the old data in the databases.

After the data purging has occurred, you can run a DBShrink command to recover the unused space.

References

 

A big thanks goes out to Joe Rodgers for assistance with this issue! Check out his blog at http://blogs.msdn.com/josrod/