Thursday, June 30, 2011

Beware of SharePoint 2010 Web Analytics on SQL Server Enterprise

imageYou might have missed this information (we did :( ) but when running your SharePoint 2010 farm on SQL Server 2008 Enterprise (or 2008 R2), the Web Analytics reporting database uses the table partitioning feature provided in SQL Server.

 

 

 

For large-scale environments, we recommend that you run the Web Analytics service application Reporting database on a server that is running SQL Server 2008 Enterprise Edition so that the Web Analytics service application can take advantage of table partitioning.

           

         From: Databases Types and Descriptions (http://technet.microsoft.com/en-us/library/cc678868.aspx)

What this means is that your reporting databases will span one additional “NDF” file (and file group, named with the Aggregation DATE suffix) every week as illustrated below:

clip_image001

We realized this while trying to setup the logshipping of this base (which is supported according to this page by the way).

Hope it helps !

10 comments:

Anonymous said...

This doesn't really help.. it's nice to know this happens but where can this be set up or managed. An extra db file each week can grow to large proportions.

dmcmillan said...

I'm also wondering how to manage these secondary data files. Looks like it's creating a new one every week. Where is that coming from? Can we change it?
Thanks very much for any information.

Joe said...

Hi dmcmillan, where it comes from is quite straightforward. table partitionning is one of the benefits of the Enterprise version of SharePoint (http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx). SharePoint is smart enough to take advantage of this feature.

As of a solution well it shouldn't really be an issue for you unless you are trying to log ship or mirror this db. In this case you need to replicate the new file creation on the target for the process to keep working.

Joe

dmcmillan said...

Thanks for the explanation.
But even though table partitioning is available, suppose you don't want to take advantage of it? Is there any long term maintenance for all these .ndf files? What happens when we have thousands of them?
Is there any setting in Sharepoint to set how often the new data files are created, like quarterly instead of weekly?
THanks again very much.

Joe said...

Hi Dmcmillan,

I checked to see how it could be possible to deactivate this table partitionning but didn't find anything. I would actually be interested in the solution as well.

Anyway, with one file per week it would take about 20 years to have 1000 files so I can live with it if I can't readily find a solution. It's more a nice to have than a must-have.

Thank you,
Joe

bluecollarcritic said...

I realize this post is from last June but I hope its still OK to comment.

My question is can we purge/drop the older files after X Increments so as to avoid DB File Spawning. I am not a SharePoint guru and so that may be a dumb question since I don't know exactly what that SP DB is for. That said its ridiculous from a DB perspective that this thing is creating a new file & file group every week. It’s bad enough that SharePoint 2010 (in general) creates new databases when the wind changes direction. Seeing it continuously spawn new file/file groups for an existing DB is ridiculous. The fact that (in our case) the DB as a whole is only 10GB tells me this file spawning by SharePoint is a poor design decision.

There are good reasons for both additional DB files & file groups and table portioning but I doubt those are in play for SharePoint; not when the DB as a whole is so small. So is there any reason we must retain every file& file group this DB generates? Is there a link or quick summary you can provide on what this DB is for any why it thinks it needs to use table portioning (or file spawning when table portioning is not available) ?

Thanks

bluecollarcritic said...

More on my prior post:

I assumed that these extra ndf files & file groups generated by SharePoint were for some purpose but a quick check shows that all files save for teh orginal mdf are unused/empty. This just further re-enforces my stance on how SharePoint’s design[er]s appear to have not been properly versed in the use of RDBMS.

1) Why would SharePoint continue to generate these additional ndf files and not use them?

2) Any reason (from the SharePoint end) I can't shrink and then drop these unused files & file groups?


Thanks again

Anonymous said...

I've searched the web and just found your site. It's really a good information on these .ndf files generated by Web Analytics.

But is there a way to truncate/remove the old ones? Without shutting down the database? We actually don't use Web Analytics. It's insane to have a database spawn like this.

Horst said...

Hello,
I have also found this ndf files at web analytics db.
How can we stop this?
Horst

Anonymous said...

Permissions issue, see this post:
http://social.technet.microsoft.com/forums/en-US/sharepointadminprevious/thread/68a07426-cc40-4b17-b084-20236be990d2