Shrinking databases in Project Server 2010
Applies to: Project Server 2010
Topic Last Modified: 2011-06-20
Sizing the deployment of any farm is an inexact science. It is best to err on the side of caution and over-allocate space in your instance of Microsoft SQL Server. Once the system achieves steady state (the number of projects being created each year is about the same as the number that you are archiving/deleting) you may be able to reduce your initial allocation to recover space on your instance of SQL Server.
Shrinking data files
You can shrink each file in a database (extensions .mdf, .ldf, and .ndf) to remove unused pages and recover disk space. Microsoft SharePoint Server 2010 databases do not automatically shrink data files, although many activities create white space in the database. Activities that can create white space include merging content databases, and deleting projects, resources, large custom fields, documents, document libraries, lists, list items, and sites within Project Workspaces.
Free space is released from the end of the file — for example, a content database file of 60 gigabytes (GB) with a specified target size of 40 GB will free as much space as possible from the bottom 20 GB of the database file. If used pages are included in the bottom 20 GB, those pages will later be relocated to the upper 40 GB of the file that is retained. You can determine the free space in a database by using the sp_spaceused stored procedure. For more information, see sp_spaceused (Transact-SQL) (https://go.microsoft.com/fwlink/p/?LinkId=221731).
You can shrink database files individually or as a group. Shrink operations are most effective following a large file or site that can potentially generate much unused space. Database files can only be reduced to the point where there is no free space remaining; therefore a content database in which content is infrequently deleted may see minimal benefit from shrinking. Repeated shrinking may result in increased fragmentation because the operation does not preserve the fragmentation state of indexes. You do not have to shrink database files as frequently as you defragment indexes. However, in environments where data is often deleted from the databases, you may want to schedule database file shrinking more frequently.
When shrinking databases, consider the following:
We do not recommend that you auto-shrink databases or configure a maintenance plan that programmatically shrinks your databases.
Shrink a database only when 50% or more of the content in it has been removed by user or administrator deletions.
We recommend that you shrink only content databases. The configuration database, Central Administration content database, SSP databases, and search databases do not usually undergo enough deletions to contain significant free space.
Avoid the need to shrink databases by including growth allocations in your capacity planning, including an overhead allocation of 10-20 percent.
Shrinking databases is a resource-intensive operation. Therefore, if you must shrink a database, carefully consider when to schedule it.
Database and database files can be shrunk manually to recover space by using Management Studio or the Maintenance Plan Wizard. For more information, see Shrinking a Database (https://go.microsoft.com/fwlink/p/?LinkId=221732).