Previously, I had written a blog post about SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation. After that, I have written the following blog post that talks about the advantage and disadvantage of Shrinking and why one should not be Shrinking a file SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008. On this subject, SQL Server Expert Imran Mohammed left an excellent comment. I just feel that his comment is worth a big article itself. For everybody to read his wonderful explanation, I am posting this blog post here about Shrinking Database. Thanks Imran!
Shrinking Database always creates performance degradation and increases fragmentation in the database. I suggest that you keep that in mind before you start reading the following comment. If you are going to say Shrinking Database is bad and evil, here I am saying it first and loud. Now, the comment of Imran is written while keeping in mind only the process showing how the Shrinking Database Operation works. Imran has already explained his understanding and requests further explanation. I have removed the Best Practices section from Imran’s comments, as there are a few corrections.
Before I explain to you the concept of Shrink Database, let us understand the concept of Database Files.
When we create a new database inside the SQL Server, it is typical that SQl Server creates two physical files in the Operating System: one with .MDF Extension, and another with .LDF Extension.
- .MDF is called as Primary Data File.
- .LDF is called as Transactional Log file.
If you add one or more data files to a database, the physical file that will be created in the Operating System will have an extension of .NDF, which is called as Secondary Data File; whereas, when you add one or more log files to a database, the physical file that will be created in the Operating System will have the same extension as .LDF.
The questions now are, “Why does a new data file have a different extension (.NDF)?”, “Why is it called as a secondary data file?” and, “Why is .MDF file called as a primary data file?”
Note: The following explanation is based on my limited knowledge of SQL Server, so experts please do comment.
A data file with a .MDF extension is called a Primary Data File, and the reason behind it is that it contains Database Catalogs. Catalogs mean Meta Data. Meta Data is “Data about Data”. An example for Meta Data includes system objects that store information about other objects, except the data stored by the users.
sysobjects stores information about all objects in that database.
sysindexes stores information about all indexes and rows of every table in that database.
syscolumns stores information about all columns that each table has in that database.
sysusers stores how many users that database has.
Although Meta Data stores information about other objects, it is not the transactional data that a user enters; rather, it’s a system data about the data.
Because Primary Data File (.MDF) contains important information about the database, it is treated as a special file. It is given the name Primary Data file because it contains the Database Catalogs. This file is present in the Primary File Group.
You can always create additional objects (Tables, indexes etc.) in the Primary data file (This file is present in the Primary File group), by mentioning that you want to create this object under the Primary File Group.
Any additional data file that you add to the database will have only transactional data but no Meta Data, so that’s why it is called as the Secondary Data File. It is given the extension name .NDF so that the user can easily identify whether a specific data file is a Primary Data File or a Secondary Data File(s).
There are many advantages of storing data in different files that are under different file groups. You can put your read only in the tables in one file (file group) and read-write tables in another file (file group) and take a backup of only the file group that has read the write data, so that you can avoid taking the backup of a read-only data that cannot be altered. Creating additional files in different physical hard disks also improves I/O performance.
A real-time scenario where we use Files could be this one:
Let’s say you have created a database called MYDB in the D-Drive which has a 50 GB space. You also have 1 Database File (.MDF) and 1 Log File on D-Drive and suppose that all of that 50 GB space has been used up and you do not have any free space left but you still want to add an additional space to the database. One easy option would be to add one more physical hard disk to the server, add new data file to MYDB database and create this new data file in a new hard disk then move some of the objects from one file to another, and put the file group under which you added new file as default File group, so that any new object that is created gets into the new files, unless specified.
Now that we got a basic idea of what data files are, what type of data they store and why they are named the way they are, let’s move on to the next topic, Shrinking.
First of all, I disagree with the Microsoft terminology for naming this feature as “Shrinking”. Shrinking, in regular terms, means to reduce the size of a file by means of compressing it. BUT in SQL Server, Shrinking DOES NOT mean compressing. Shrinking in SQL Server means to remove an empty space from database files and release the empty space either to the Operating System or to SQL Server.
Let’s examine this through an example.
Let’s say you have a database “MYDB” with a size of 50 GB that has a free space of about 20 GB, which means 30GB in the database is filled with data and the 20 GB of space is free in the database because it is not currently utilized by the SQL Server (Database); it is reserved and not yet in use. If you choose to shrink the database and to release an empty space to Operating System, and MIND YOU, you can only shrink the database size to 30 GB (in our example). You cannot shrink the database to a size less than what is filled with data.
So, if you have a database that is full and has no empty space in the data file and log file (you don’t have an extra disk space to set Auto growth option ON), YOU CANNOT issue the SHRINK Database/File command, because of two reasons:
- There is no empty space to be released because the Shrink command does not compress the database; it only removes the empty space from the database files and there is no empty space.
- Remember, the Shrink command is a logged operation. When we perform the Shrink operation, this information is logged in the log file. If there is no empty space in the log file, SQL Server cannot write to the log file and you cannot shrink a database.
Now answering your questions:
(1) Q: What are the USEDPAGES & ESTIMATEDPAGES that appear on the Results Pane after using the DBCC SHRINKDATABASE (NorthWind, 10) ?
A: According to Books Online (For SQL Server 2000):
- UsedPages: the number of 8-KB pages currently used by the file.
- EstimatedPages: the number of 8-KB pages that SQL Server estimates the file could be shrunk down to.
Important Note: Before asking any question, make sure you go through Books Online or search on the Google once. The reasons for doing so have many advantages:
1. If someone else already has had this question before, chances that it is already answered are more than 50 %.
2. This reduces your waiting time for the answer.
(2) Q: What is the difference between Shrinking the Database using DBCC command like the one above & shrinking it from the Enterprise Manager Console by Right-Clicking the database, going to TASKS & then selecting SHRINK Option, on a SQL Server 2000 environment?
A: As far as my knowledge goes, there is no difference, both will work the same way, one advantage of using this command from query analyzer is, your console won’t be freezed. You can do perform your regular activities using Enterprise Manager.
(3) Q: What is this .NDF file that is discussed above? I have never heard of it. What is it used for? Is it used by end-users, DBAs or the SERVER/SYSTEM itself?
A: .NDF File is a secondary data file. You never heard of it because when database is created, SQL Server creates database by default with only 1 data file (.MDF) and 1 log file (.LDF) or however your model database has been setup, because a model database is a template used every time you create a new database using the CREATE DATABASE Command. Unless you have added an extra data file, you will not see it. This file is used by the SQL Server to store data which are saved by the users.
Hope this information helps about Shrinking Database. I would like to as the experts to please comment if what I understand is not what the Microsoft guys meant.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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!
Is a very good explanation about Shrinking DataBase and how it works
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.
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.
DBCC SHRINKFILE (N’UMG_GIMM_EDW_FG1_Dat1′ , 2331220, TRUNCATEONLY)
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
***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.
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.