Configure Disk Space Usage (PowerPivot for SharePoint)
A PowerPivot for SharePoint deployment uses the disk space of the host computer to cache PowerPivot databases for faster reloads. Every PowerPivot database that is loaded in memory is first cached to disk so that it can be quickly reloaded later to service new requests. By default, PowerPivot for SharePoint uses all available disk space to cache its databases, but if you installed SQL Server 2008 R2 Service Pack 1 (SP1), you can set properties that limit how much disk space is used.
This topic explains how to set the limits on disk space usage.
This topic does not provide guidance for disk space management of PowerPivot databases (embedded in Excel workbooks) that are stored in content databases. PowerPivot databases can be large, thereby placing new demands on the storage capacity of the farm. In addition, if versioning is enabled, you might easily have multiple copies of the data in the same content database, further increasing the amount of disk space required for content storage. Although PowerPivot databases are an important consideration for disk management, they are not something that can be managed independently of other content that you store in a SharePoint farm. You will need to monitor disk space more closely as your business increases its use of PowerPivot workbooks. You can also track PowerPivot workbook activity in the PowerPivot Management Dashboard and remove workbooks that are no longer used.
How PowerPivot for SharePoint manages cached databases
To manage its cache, the PowerPivot System Service runs a background job at regular intervals to clean up unused or outdated databases that have newer versions in a content library. The purpose of the cleanup job is to unload inactive databases from memory and to delete unused, cached databases from the file system. The cleanup job is for long-term maintenance, ensuring that databases do not remain on the system indefinitely. On an active server, databases might be removed more often due to memory pressure on the server, database deletion in SharePoint, or newer versions of the database in a content library.
Although you cannot schedule the cleanup job, you can customize cache file management by setting server configuration properties that do the following:
Set limits on the amount of disk space used by the cache.
Specify how much data to delete when maximum disk space is reached.
How to check disk space usage
PowerPivot for SharePoint is installed on application servers in a SharePoint farm. Each installation has a data directory that includes a Backup folder. The Backup folder contains all of the data files that are cached by the Analysis Services instance on the computer. By default, the Backup folder can be found at the following path:
%drive%:\Program Files\Microsoft SQL Server\MSAS10_50.PowerPivot\OLAP\Backup\Sandboxes\<serviceApplicationName>
To check how much total disk space is used by the cache, you have to check the size of the folder. There is no property in Central Administration that reports on current cache size.
The Backup folder provides common cache storage for any PowerPivot database that is loaded in memory on the local computer. If you have multiple PowerPivot service applications defined in your farm, any one of them can use the local server to load and subsequently cache PowerPivot data. Both data loading and caching are Analysis Services server operations. As such, total disk space usage is managed at the Analysis Services instance level, on the Backup folder. Configuration settings that limit disk space usage are therefore set on the single SQL Server Analysis Services instance that runs on a SharePoint application server.
The cache contains only PowerPivot databases. PowerPivot databases are stored in multiple files under a single parent folder (the Backup folder). Because PowerPivot databases are meant to be used as internal data to an Excel workbook, database names are GUID based rather than descriptive. A GUID folder under <serviceApplicationName> is the parent folder of a PowerPivot database. As PowerPivot databases are loaded on the server, additional folders are created for each one.
Because PowerPivot data might be loaded on any Analysis Services instance in a farm, the same data might also be cached on multiple computers in the farm. This practice favors performance over disk space utilization, but the tradeoff is that users get faster access to data if it is already available on disk.
To immediately reduce disk space consumption, you can shut down the service and then delete a PowerPivot database from the Backup folder. Manually deleting files is a temporary measure, as a newer copy of the database will be cached again the next time the PowerPivot data is queried. Permanent solutions include limiting disk space used by the cache.
At the system level, you can create email alerts that notify you when disk space is low. Microsoft System Center includes an email alert feature. You can also use File Server Resource Manager, Task Scheduler, or PowerShell script to set up alerts. The following links provide useful information for setting up notifications about low disk space: File Server Resource Manager Step-by-Step Guide for Windows Server 2008 R2 and Setting low disk space alerts on Windows Server 2008.
How to limit the amount of disk space used for storing cached files
In Central Administration, in Application Management, click Manage Services on Server.
Click SQL Server Analysis Services.
Notice that limits are set on the Analysis Services instance that runs on the physical server, and not at the service application level. All service applications that use the local Analysis Services instance are subject to the single maximum disk space limit that is set for that instance.
In Disk Usage, set a value (in gigabytes) for Total disk space to set an upper limit on the amount of space used for caching purposes. The default is 0, which allows Analysis Services to use all available disk space.
In Disk Usage, in the Delete cached databases in last ‘n’ hours setting, specify last-used criteria for emptying the cache when disk space is at the maximum limit.
The default is 4 hours, meaning that all databases that have been inactive for 4 hours or more are deleted from the file system. Databases that are inactive but still in memory are unloaded and then deleted from the file system.
See Also