How to properly prune Web Analytics reporting database NDF files

Question

As most are aware of the reporting component of the Web Analytics service application creates a new NDF database file each week for data aggregations.  I have set the reporting data retention period to 3 months on my service application, however I am noticing that NDF files older than 3 months are still there. Is there a proper procedure for pruning these old files? Can i simply remove them with SQL tools?

What exactly does the ReportingDataRetentionPeriod property on my service application refer to? 

Also, when i originally sized this database I set a large initial size of the primary MDF file. Since it appears that the service app doesn't even use the MDF but rather creates the NDF files, can I reduce the size of the MDF and assume that the primary disk usage will only come from the multiple NDF files?

Answer 1

Hi,

As far as I know, once per week, SharePoint executes an "ALTER DATABASE ADD FILEGROUP" command on the  Web Analytics Reporting Database. This is by design. However, in SQL 2008 you can schedule to shrink the files and released unused space. For more information about shrinking data files, check out the following article:

http://technet.microsoft.com/en-us/library/cc262731.aspx#DBMaintenanceForSPS2010_ShrinkingDataFiles

In addition, you can run the following command to reduce the numbers of days that usage data for the usage provider is retained in the usage service database.

Set-SPUsageDefinition -Identity "Page Requests" -DaysRetained 1

Run this command for all the events and reduced from 14 days to 1 day.

 Sandboxed Reuest  14  True

 Content Import Usage  14 True

 WorkFlow   14 True

 Clickthrough Usage  14 True

 Content Export Usage  14 True

 Page Request   14 True

 Feature Use   14 True

 Search Query Usage  14 True

 Site Inventry Usage  14 True

 Sandboxed Requests Monitored 14 True

 Timer Jobs   14 True

 Rating Usage   14  True

Thanks,

Rock Wang

---

Rock Wang TechNet Community Support

Answer 2

Is it possible to delete the old files altogether?

My Usage application is running the way I want it to. I am strictly referring to Web Analytics reporting.

Answer 3

Has anyone needed to do this?

Answer 4

I am going to attempt to consolidate the NDF files back into the PRIMARY file group

it seems that each one creates it's own filegroup (parititoning??)

even SQL query I have tried has thrown basically the same error.

I can't empty the NDF files because there is no other file/pages available in that partition/filegroup and then you can't remove the NDF file because it isn't empty.

I spoke with a colleague from a major consulting/implementation firm. He believe you should be able to consolidate the NDF using database maintenance jobs

i will let you know

Answer 5

I would appreciate that. Please post with the outcome.

Answer 6

Hello,

It seems by default the web analytics service application retains the 30 day reports for 25 months.

In central admin -> manage service applications -> web analytics -> properties

I changed the retention period to 2months and over the weekend 70% of the NDF files disappeared during background jobs / configured sharepoint sql maintenance jobs.

The default value is 25.

I may end up turning back up the rentention period to 6months+

It depends if this impacts any of my 3rd party admin tools but I am hopeful it will not since they use SQL Reporting and Analysis services on a different database backend.

Answer 7

I changed the retention period to 2months and over the weekend 70% of the NDF files disappeared during background jobs / configured sharepoint sql maintenance jobs.

Gantense,

I have had my retention period set to 3 months since the beginning and I still have NDF files from October 2011. The background jobs/configured sharepoint maintenance jobs, are these enabled by default? It would appear these aren't running in my case.

Answer 8

tpullins,

I have just had a resolution to this issue (had problem where old NDF files not deleted). The NDF files are deleted by the ReportConsolidator component (part of the Web Analytics process) which runs daily at 2:00am. If you check the ULS log on the Farm server(s), where the Web Analytics Service is running around this time you may see:

Failed to execute DataConsolidator compoenet. Access denied.
Failed to execute the Web Analytics component ReportConsolidator. Exception ...

In our case we were running the Web Analytics Service under a Service Application account (which is actually recommended by Microsoft documentation). We changed to the Farm Administration account and all the old NDF files were deleted in the next run.

Answer 9

Claude I had hope for your solution as I also set my web analytics to run under a different service account. Sure enough, when I set it to run under the farm account the old data is deleted! Thank you! I never would have caught the ULS logs at 2AM.

Answer 10

I had the same issue, nevertheless I assumed that this will happen If I change the Farm Admin account to somerhing else. But If Microsoft said so that we need to change this Service Account. What should be the permissions for the account and where? Do you know any links about that?

Answer 11

The account needs to be a member of the farm administrators group, reference: http://technet.microsoft.com/en-us/library/cc263445.aspx . Also determined that the new files are created at 2AM and the old files deleted at 4AM (using SQL Profiler). Apparently these times are configurable but you need to use Powershell commands.

Answer 12

The solution at my environment, I get the information from Microsoft:

Give WebAnalytics Service User (CA -> Security -> Configure Service Accounts -> Windows Service - Web Analytics Data Processing Service ) DB Owner rights at booth Database's.

Then, a timer job at 2:00h A.M. clean up the ndf files.

---

Thanks Horst MOSS 2007 Farm; MOSS 2010 Farm; TFS 2010; IIS 7.5

Answer 13

Hey Guys

What is the exact JOb running on Sharepoint that cleans this up.