Shrinking databases in Project Server 2007
This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.
Topic Last Modified: 2016-11-14
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 SQL Server. Once your system achieves steady state (the number of projects being created each year is about the same as the number 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
In SQL Server 2005 and SQL Server 2000, you can shrink each file within a database (extensions .mdf, .ldf, and .ndf) to remove unused pages and recover disk space. SharePoint Products and Technologies databases do not automatically shrink data files, although many activities create white space in the database. Activities that can create white space include running the Stsadm mergecontentdbs operation, 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 subsequently 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/?LinkId=128515\&clcid=0x409).
You can shrink database files individually or as a group. Shrink operations are most effective following a large file or site that has the potential to generate a large quantity of 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 need 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 executing the DBCC SHRINKFILE and DBCC SHRINKDATABASE statements, by using SQL Server 2005 Management Studio, or by using the SQL Server 2005 Maintenance Plan Wizard.
Shrinking a database by using Transact-SQL commands
DBCC SHRINKDATABASE shrinks the data and log files for a specific database. To shrink individual files, use DBCC SHRINKFILE. For more information, see DBCC SHRINKDATABASE (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=128516\&clcid=0x409) and DBCC SHRINKFILE (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=128517\&clcid=0x409).
You can also shrink a database using SQL Server Management Studio. For more information, see How to: Shrink a Database (SQL Server Management Studio) (https://go.microsoft.com/fwlink/?LinkId=128518\&clcid=0x409).