Previously, I had written a blog post about SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation. Following that, I wrote another post discussing the advantages and disadvantages of shrinking database files and why one should generally avoid this operation: SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Shrinking databases is a topic that requires clarity because it can significantly impact performance and database health. Let’s delve into the technical details of this process, its implications, and practical considerations.
Shrinking Database: Overview
Shrinking a database often leads to performance degradation and increases fragmentation within the database. It is important to understand these risks before proceeding with a shrink operation. While shrinking may seem like an easy fix for reducing database size, it is typically considered a poor practice and should be avoided unless absolutely necessary.
Understanding Database Files
When creating a new database in SQL Server, the system typically generates two physical files in the operating system:
- Primary Data File (.MDF) – This file contains critical metadata, such as database catalogs.
- Transaction Log File (.LDF) – This file stores transactional data to maintain database integrity.
If additional data files are added, they are created with a .NDF extension and are referred to as Secondary Data Files. These files store user data but do not contain metadata.
Why the File Types Matter
- Primary Data File (.MDF): Stores metadata like system objects that describe the structure of the database (e.g.,
sysobjects,sysindexes,syscolumns). - Secondary Data File (.NDF): Stores transactional data and helps expand storage across multiple drives.
- Log File (.LDF): Records all transactions and supports recovery operations.
The distinction between these file types is essential for database management, performance optimization, and backup strategies.
The Shrinking Process: How It Works
In SQL Server, shrinking does not mean compressing data. Instead, it involves removing unused space from database files and releasing it back to the operating system. For example:
- If a 50GB database contains 30GB of data and 20GB of unused space, shrinking can reduce the file size to 30GB.
- Shrinking cannot reduce the file size below the amount of data stored in it.
Key Points to Remember – Shrinking Database
- Shrinking is a logged operation, meaning every action is recorded in the transaction log.
- If the log file does not have enough free space, the shrink operation cannot proceed.
- Shrinking increases fragmentation, which can degrade query performance.
Practical Scenarios for Database Management
Consider a scenario where your database has outgrown its storage drive. Instead of shrinking the database:
- Add a new physical hard disk.
- Create a new Secondary Data File (.NDF) on the new drive.
- Migrate some objects (tables, indexes) to the new file.
This approach avoids unnecessary fragmentation and improves I/O performance.
Common Questions About Shrinking Databases
1. What are UsedPages and EstimatedPages in DBCC SHRINKDATABASE?
- UsedPages: The number of 8-KB pages currently in use by the file.
- EstimatedPages: The number of 8-KB pages the file could shrink to.
2. What is the difference between shrinking via DBCC commands and the GUI?
Both methods work the same way. However, using DBCC commands allows you to continue working in the management console without freezing it.
3. What is a .NDF file?
An .NDF file is a Secondary Data File used to store additional data. By default, SQL Server creates databases with only one .MDF (Primary Data File) and one .LDF (Log File). Additional .NDF files are created when needed to extend storage.
Key Takeaways
- Avoid shrinking databases unless absolutely necessary. It leads to fragmentation and performance issues.
- Use secondary data files and distribute them across physical disks to optimize performance and manage storage effectively.
- Understand that shrinking does not compress data; it only removes unused space.
If you have further questions, feel free to share them in the comments. Let’s continue to explore and learn more about effective database management. Follow me on Twitter for updates: Twitter
Reference: Pinal Dave (https://blog.sqlauthority.com)
