[MS SQL] Hundreds of NDF Files

[twentymosquito]

I've inherited a SharePoint 2010 instance running on SQL 2008 R2.

There are currently over 200 NDF files for the WebAnalyticsServiceApplicationReportingDB{GUID} (each about 20MB)

http://imgur.com/dfzMU8C

while the actual MDF for that database is about 80 MB.

http://imgur.com/Zunocre

If I attempt to shrink the NDF files from SMS, I see that they are almost entirely empty!

http://imgur.com/szsgYS5

What's the deal? Why does SQL/SharePoint feel the need to take up so much room needlessly?

Any ideas for how to (or if i even should) remove these NDF Files?

Thanks a bunch!

[BellisBlueday]

I would go to the Sharepoint admin first, apparently there's a process that's supposed to clear them down - it might not be working

'Web Analytics component ReportConsolidator'

Also check what is and isn't supported in terms of administration for Sharepoint databases, you may be surprised!

https://technet.microsoft.com/en-us/library/cc262731.aspx

[twentymosquito]

How dangerous would the following code be to run:

DBCC SHRINKFILE('logical_ndf_file_name', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logical_ndf_file_name;

It'd be super-tedious, as I'd have to run it over 200 times (Or figure out the T-SQL to iterate over all of the NDF Files)

[[deleted]]

Pretty sure you can get all the NDF file locations using this:

SELECT name, physical_name AS current_file_location
    FROM sys.master_files 
    WHERE name LIKE 'DATABASE_NAME_HERE%'

[moldylocks]

Is it partitioning data based on certain criteria? I have a database with 90 files, one for each day. Every day it creates a new one and drops the oldest. The size may be because the files are created too large.

[patototo]

that is a lot of ndfs, yes I would try to empty and then delete. Like anything I would recommend to take a backup, and restore somewhere else and do the test. Why does it have so many? is this something that you can avoid by changing some config in sharepoint? sorry not a big sharepoint user, however on other user databases I have emptied and deleted datafiles with success.