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)
20 Comments. Leave new
Great writeup from Imran and you, nicely laid out concepts about .mdf,.ndf,.ldf and shrinking files. There have been discussions about how shrink options need to be made more restricitve that make it available at a click of a buttion. This can solve lot of problems associated with shrinking.
Thanks for the tutorial.
I gained a lot while reading this.
Thanks For this article , it will helps lot to me…
great job imran
Great Article !
Thanks for the wonderful article.
It was very informative and clear….!!!
this was great I didnt know that !
as m new with ms sql
i am able to export data to text file in sql server 2005 using cmd shell command. but i am not able to export clomns please help me
When adding this extra files, should DBA name the secondary files .ndf, or does the RDBMS do this automatically….?
I am experimenting with one of our (critical and huge) databases that initially was created with two physical files, .mdf and ldf. But now I wanted to split the things up a bit, and I added several logical/physical files, both in the same filegroup, and in additional filegroup. When adding those files, I chose their extensions to be .mdf, and there are several .mdf files and one .ldf file now – no .ndf’s.
I would also like to post a question about the possibilities for partitioning a database (i.e. spread it across several physical files) – I experimented with partitioning some tables, but that seems to be too complicated…
Thanks in advance!
Regards, Zoran
Is a very good explanation about Shrinking DataBase and how it works
Zoran,
The .ndf extension is suggested by microsoft as a easy way to identify your secondary data files from primary data files (.mdf), you can use any extension you want in those files, always trying to be consistent.
In my humble opinion i recommend you to use the “standard” extensions (.mdf, .ndf, .ldf) for your DB files in case of any other DBA get his hands on them, that will be easier to undestand.
What about space that is made available with a delete? Is this space not used by SQL until such time as a shrink is done?
Shrinking database partitions to reclaim space is not arbitrarily bad as you and your ilk happily seem to suggest.There are some very good reasons to do it as soon as you get beyond the trivial world where most of your readers live.
When using partitions in a circular rotation, it is desirable to shrink emptied partitions to a minimum size until they are next in the rotation. I my test example I have 12 partitions (1 per month). I only wish to keep 3-4 months of data online, deleting 1 day at a time and shrinking the partition when it is empty (where is TRUNCATE table (partition_id) when you need it? MIA and no where in sight – what else should one expect from MS). This approach reduces the physical disk space requirements by ca. 2/3.
My issue now is that in my long running test scenario, it is now in fact impossible to shrink some of the empty partititions, and I am left with wasted space that I cannot reclaim. For some reason the ability to reclaim space has broken in this test database.
I am running 2008R2 (10.50.1797)
If you have any clues why an empty partition might not shrink other than SQLServer being broken, I would be happy to hear them.
Hello,
We are trying to shrink our database from 2.4TB to 1.5TB. Most of the data is on one data file. We ran the operation below and we specify using TRUNCATEONLY so that the shrink operation doesn’t get logged.
The operation completed but the OS or SQL Server has not given back the space.
Any feedback on why the SHRINK operation would not fully complete?
We’ve investigated many angles, but still haven’t been able to find the bottleneck.
Thanks,
Coy Bernardo
USE [UMG_GIMM_EDW]
GO
DBCC SHRINKFILE (N’UMG_GIMM_EDW_FG1_Dat1′ , 2331220, TRUNCATEONLY)
GO
Hi Coy,
The command you are using to shrinkfile only returns free space at the end of the file to the operating system, so it is possible that the database does not have any free space at the end of the file.
instead the data file may have free space(unallocated pages) at the beginning or in the middle of the file, so in order to get this free space to the end of the file we have to do a
DBCC SHRINKFILE(*****,*****, NOTRUNCATE)
We have to use the NOTRUNCATE option to get the free space to the end of the data file.
NOTRUNCATE rearranges all the objects in the database and pushes the unallocated pages to the end of the file
After you do a NOTRUNCATE you then run the following Command to release the free space at the end of the file to the OS
DBCC SHRINKFILE(****,****,TRUNCATEONLY)
***Please note SHRINKFILE(***,***,NOTRUNCATE) is not advisable as it rearranges all the pages in a database (actually shriking is not advisable!!)
***NOTRUNCATE option is also only applicable to Data file not Log files.
NOTE:-I would advise you to rebuild all your indices(index’s) after you perform this.
bear in mind shrink NOTRUNCATE would take loads of time as it rearranges all the pages in a database ….
Thank you Addie, that makes sense and I definitely hear your warning about shrinking.
So to my understanding, if I do a DBCC SHRINKFILE without a NOTRUNCATE or a TRUNCATEONLY it will both rearrange the pages and release the space at the end of the file? Is that correct?
Thanks again. Your input really helped us out.
Coy
No! TruncateOnly does not rearrange the pages it just releases the unallocated space at the end of the file to the operating system and hence the file appears to be shrunk.
whereas NoTruncate rearranges all the pages and brings unallocated pages to the end of the file but DOES NOT release the free space back to the operating system.(***file does not appear to have shrunk, it remains the same size)
BEAR IN MIND
TRUNCATEONLY:- NO REARRANGE just Release empty space at the end of the file
NOTRUNCATE:- ONLY REARRANGE but does not release space to the OS. the size of the file remains the same..
so do a NOTRUNCATE to get the free pages to the end of the file and then do a TRUNCATEONLY to release the space back to the OS(i mean..SHRINK THE FILE PHYSICALLY)
Looks like the you are on the assumption that a Primary Data File must have a .mdf extension, and a Secondary Data File must have a .ndf extension, which is wrong.
SQL Server DO NOT enforce the .mdf/.ndf/.ldf File Name extensions.
For more info, please refer:
Thanks Addie, NoTruncate followed by Truncateonly worked perfectly.
Thank you so much.