Which files can be deleted in \OLAP\Data\TFS_Analysis.0.db\File.0.dim

Question

We have a TFS 2010 installation which has an SSAS instance, the Tfs_Analysis db is pretty small, (under 1 GB) but there is a vast number of files in the the following directory

\OLAP\Data\TFS_Analysis.0.db\File.0.dim\

There are in fact about 250,000 files, taking up 280GB of disk space, this seems odd when the DB is under 1 GB.

There are for example 55GB of files in this directory which are between 6 and 12 months old.  Can these files be deleted without interruptin the analysis cube?

Regards

Marcus

 

 

 

Answer 1

Hi Marcus,

 

Thanks for your post.

 

You said your Tfs_Analysis DB is under 1 GB, but the files under \OLAP\Data\Tfs_Analysis.0.db\File.0.dim taking up 280GB of disk space.

 

I want to confirm with you that where did you check your Tfs_Analysis DB is under 1 GB?

 

We don’t recommend to delete the DB files directly, you can try to recreate the Data warehouse and Analysis service, please refer to: http://mohamedradwan.wordpress.com/2010/10/29/how-to-recreate-the-data-warehosue-and-the-analysis-service-for-tfs/.

 

 

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi John

 

Apologies, having looked at the DB properties in SQL Management Studio it does suggest the DB properties are in fact ~280GB.

 

My question to you therefore, is this;

 

What will happen to all the files in '\OLAP\Data\Tfs_Analysis.0.db\File.0.dim' if I carry out a 'recreate' as described above?

Thanks

Marcus

Answer 3

Hi Marcus,

 

Thanks for your reply.

 

I think the files under that path will be recreated. Before operate DB directly, we suggest you to create the full backup.    

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Hi John

 

Thanks for the information, very useful.

 

If this is the case, our Analysis Service drives are going to continue filling up pretty quickly.  is there a way to limit the size of the DB growing beyond a certain size?

 

For example, could we stop the Analysis Services DB becoming larger than 300GB?

 

Regards

Marcus

Answer 5

Hi Marcus,

 

Thanks for your reply.

 

I’m glad to hear that the information is useful for you.

 

For limiting the size of Analysis Service DB issue, I think it’s related to SQL Server, so I suggest you to post it at http://social.msdn.microsoft.com/Forums/en-US/category/sqlserver for quicker and better response, where SQL Server experts live in.

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 6

Hi Marcus,

Did you ever resolve the issue with thousands of files in the File.0.dim folder for the AS cube? Any tips for me?

I have the same problem. The files date back several months, and I'm not sure if that is because of a problem in AS keeping too many files from the past, or if they are not getting cleaned up properly when the processing runs. I think Incremental processing (aka ProcessAdd/ProcessUpdate in AS) does cause file bloat under normal circumstances, but I don't know if that is the root cause of the problem. Maybe I just need to run Process Full to clear the history of incremental changes to the data, since AS will delete the files in normal conditions when ProcessFull is run.

I am thinking to try a Process Full via the web service as follows, but I'm not sure if you already tried it and found no luck to minimize the size.

http://msdn.microsoft.com/en-us/library/ff400237.aspx

See heading Process the Analysis Services Cube

1. From the WarehouseControlWebService page, click ProcessAnalysisDatabase.
2. On the ProcessAnalysisDatabase page, in processingType, type either Incremental or Full, and then click Invoke.

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 7

I think I found the answer is that there is an Analysis Services bug.

In the TFS cube created by Visual Studio Team Foundation Server 2010, inside the data folder [File.0.db] there are hundreds or thousands of sets of multiple files each with the naming pattern as shown:

{
1.FileSK.ParentFileSK1.fact.map

1.FileSK.ParentFileSK1.fact.hdr
...

1.FileSK.ParentFileSK21.fact.map

1.FileSK.ParentFileSK21.fact.hdr
......



9999.FileSK.ParentFileSK1.fact.map
9999.FileSK.ParentFileSK1.fact.hdr

...

9999.FileSK.ParentFileSK21.fact.map
9999.FileSK.ParentFileSK21.fact.hdr

}

Note This issue occurs in Microsoft SQL Server 2008 and 2008 R2.

http://support.microsoft.com/kb/979778

Install the hotfix from 2008 R2 RTM CU2 or later, SP1 service pack, or the latest service Pack SP3.

Afterwards, rebuild the cube to get the extra files to drop.
TFS does that nightly at 3am in this case, or ProcessAdd every 2 hours, so it will heal itself I believe.

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance