Brad’s Sure Guide to SQL Server Maintenance Plans- P21: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 6 Shrink Database Task In Chapter 3 I recommended that you never use the Shrink Database task and that advice stands firm. In this brief chapter I will describe exactly what is meant by shrinking a database why I advise against doing it using the Shrink Database task in the Wizard. I will also explain the legitimate reasons to shrink your databases and the right ways to do it. Sizing Your Database Files A database is composed of at least two physical files one MDF data file where data is stored and one LDF log file where the transaction log is located. A database can actually have more than two physical files but for the sake of this discussion we ll assume it comprises one MDF file and one LDF file. When a new database is created using default settings the initial size of the MDF and LDF files will be small their autogrowth setting will be turned on and file growth will be set to unrestricted. Using these default values is unsuitable for many databases. In busy databases these files can grow rapidly and be subject to frequent autogrowth events. These events are resource-intensive and can have a dramatic impact on the performance of your server when they occur. As well as inducing potentially unnecessary autogrowth events this incremental file growth can cause other issues such as increasing physical file fragmentation and the creation of excessive virtual log files within the log file. A full discussion of these issues and of how to correctly size your data and log files is beyond the scope of this book but the salient point here is that it is a recommended best practice to pre-size the physical data and log files to their estimated future production sizes. In other words when you create a database you should size these files not only so that they can cope with the current volume of data but also so that they can accommodate predicted future growth for the coming year for example . The autogrowth feature should be left activated but the DBA should not rely
