SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation

Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you.

Hello Pinal,

I have seen many blogs from you on SQL server and i have always found them useful and easy to understand. Thanks for all the information you provide.

I have one query about shrinking NDF and MDF files.

Can we shrink NDF and MDFfiles?? If you do so is there any data loss?

I have been shrinking the .LDF files every now and then but I am not too sure about NDF and MDF files.

Can you please answer my query.

Waiting for your early response.

Regards,

Siddhi

Answer:

Shrinking MDF and NDF file is possible and there is no chance of data loss.

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.

Hi Pinal,
Thanks for nice articles :-)
Shrinking a Data File brings considerable percentage of Logical Fragmentation and I don’t recommend it.
Shrink on Log File doesn’t have such impact.
The best solution to shrink a Data File without fragmentation is to re-create all indexes and move them to a new Filegroup. This operation will reorganize Data Pages, recovers from fragmentation and doesn’t waste Pages. Hence shrinkage is achieved!

Hi Pinal,
I came across the problem of Tempdb database ndf files.
I created 8 ndf files and put them in a drive having 100gb’s size.
All mdf,Ldf and ndf files were supposed to be created there.
All gave file size as 15.5 gbs.
System could not create all the files due to space restriction.I worked around and brought back mdf and ldf back t o 4gbs as required.
But I am unable to reduce ndf files.I cannot modify,add,delete those files.I cannot see any logical file information through “sp_helpfile”, but physical file is getting created each time i delete ndf files or restart the service.
I believe I might need to increase the drive space to let it create all the files so that SQL engine can commit the change in sys table.
Please guide me for best solution.

To reduce the size of .ndf files first take the database in single user mode and then use DBCC SHRINKFILE command with new file size.

However, please note that it is absolutely not recommended to shrink the files as they will create fragmentation in your file and it will reduce the performance. You need to proper set up the backup strategy.

NO NO NO…. Shrinkfile or alter db will work only on default logical files and not on files added further in single/minimal configuration mode.
Moreover in my scenario logical NDF files do not exist so there is no point that you can shrink these files.
I believe as I said earlier increasing drive space is the only viable option as of now.
Please comment.

I think by temp files you mean .ndf files. Yes, its recommonded that you do not shrink .ndf files as they cause fragmentation in tables data.
If you shrink then perform the rebuild or reindex to all indexes.

Hello Pinal, Is shrinking logfile of tempdb database advisable. I have two tempdb log files each on different drive. Now one of my drive is full and it has other database datafiles. I want to shrink the templog file which is in this drive. And if I shrink the templog will I require disk free space on the drive for shrink operation?
Thanks,
Anil.

I am trying to SHRINK an .ndf . It is one of many. All of the others will shrink. One keeps telling me “File ID 15 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty”.

I check running procedures and there are none. Can you tell me how to discover the culprit ? There is very little disk space on that volume. Could this be the real cause ?

(1) What are the USEDPAGES & ESTIMATEDPAGES that
appear on the Results Pane after using the
DBCC SHRINKDATABASE (NorthWind, 10) ??

(2) 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
Select SHRINK Option, on a SQL Server 2000 environment ?

With all due respect, this seems to be a case where proper database sizing was not made since the very beginning. What you can do, and this is just my two cents, is to monitor the growth of your DB over time, and start from there. I’m pretty sure that you’ll need to add additional disks if moving historical data to a data warehouse is not an option.

Data file with .MDF extension is called as Primary Data File, the reason behind it is, it contains Database Catalogs.

Catalogs means Meta Data. Meta Data is Data about Data.

An example for meta data includes system objects that stores information about other objects, but not the data stored by 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 each table has in that database.
sysusers stores how many users that database has.

So Metadata is storing information about other objects, meta data is not the transactional data that user enters, its a system data about the data.

Because Primary Data File (.MDF) has important information about database, it is treated special and is given the name, Primary Data file, because it contains Database Catalogs. This file is present in Primary File Group.

You can always create additional objects (Tables, indexes etc.) in Primary data file (This file is present in Primary File group), by mentioning that you want to create this object under primary file group.

Any additional data file that you add to the database will have only transactional data but no metadata, and is why called as Secondary data file with the extension .NDF, so that you can easily identify Primary Data File and Secondary Dat File(s).

There are many advantages of storing data in different Files (Under different filegroup). You can put your read only tables in one file (file group) and read write tables in another file (file group) and take backup of only filegroup that has read write data, so that you can avoid taking backup of read only data that never changes. Creating additional files on different physical hard disks also improves I/O performance.

A real-time scenario where we use Files could be,
Say, you have created a database MYDB on D-Drive, it has 50 GB Space. And you have 1 database file (.MDF) and 1 Log File on D-Drive and say all of that 50 GB space has been used and you do not have any free space left, you want to add additional space to the database, one easy option would be to add one more physical hard disk to server, add new data file to MYDB database and create this new data file on new hard disk and move some of the objects from one file to other file, and make the filegroup under which you added new file as default File group, so that any new object that is created gets created in new files, unless specified.

Now that we got basic idea of what data files are and what type of data they store and why they are named the way they are, lets move to next topic, Shrinking.

First of all, I disagree with Microsoft terminology for naming this feature as Shrinking. Shrinking in regular terms means reducing size of a file by compressing it. BUT in SQL Server, shrinking DOES NOT means compressing, shrinking in SQL Server means, removing empty space from database files and releasing the empty space either to operating system or to SQL Server.

Lets understand this with an example.
Say you have a database MyDB with size 50 GB and Free Space 20 GB, what this means is, 30GB in the database is filled with data and 20 GB of space is free in the database that is not currently utilized by SQl Server(Database), it is reserved but not in use. If you choose to shrink database and to release empty space to Operating system, MIND YOU, you can only shrink the database size to 30 GB (in our example) , you cannot shrink the database to size less than space filled with data.

So, if you have a database that is full with no empty space in data file and log file (you dont have extra disk space to set Auto growth option ON), YOU CANNOT issue SHRINK Database/File command, because of two reasons,

1. There is no empty space to be released, because shrink command does not compress database, it only removes empty space from the database files and there is no empty space.
2. Remember, Shrink command is a logged operation, When we perform Shrink operation, this information is logged in log file, and if there is no empty space in log file, SQl Server cannot write to log file because there is no empty space in logfile and that is why you cannot shrink a database.

Say, you have a database file in database MYDB, reserve space for this file is 50 GB with 20 GB free space (Which means 30 Gb is occupied space). Dont shrink this file from 50 GB to 30 Gb in one shot. shrink in small intervals like shrink <5GB in 1 shot, repeat this process 4 times. This will be more effective.

3. Do not shrink your database when running backup jobs, backup jobs will fail.
4. Always Rebuild your indexes after you shrink database, because you are removing empty space from the database, this means data pages will be rearranged creating lot of (External and Internal) fragmentation. SQL Server 2008, how ever provides an option that can aviod this case (ofcourse by adding extra overhead on the server).
Note: Rebuilding Clustered Indexe will put a lock on the tables and table will not be available for use untill SQl Server rebuilds clustered index on the table, So do not rebuild your clustered indexe when users are connected to the database or when database is in use.
5. Do not Stop Shrink Process in middle, If stopped database status might be changed from ONLINE to some other status, in simple words, if you do not have a database backup, you are SCREWED BIG TIME. Don;t ever try to attempt to stop Shrink Command manually, give time to complete its process.

Now answering your question,
(1) What are the USEDPAGES & ESTIMATEDPAGES that appear on the Results Pane after using the DBCC SHRINKDATABASE (NorthWind, 10) ??
According to Books Online (For SQl Server 2000), it means
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 google once.
Reason for doing so have many advantages,
1. if some one else already have had this question before, changes that it is answered are more than 50 %.
2. This reduces your waiting time for the answer.

(2) 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 Select SHRINK Option, on a SQL Server 2000 environment ?
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 wont be freezed. You can do peform your regular activities using Enterprise Manager.

(3) what is this NDF file that is discussed above. Never heard of it. What is it used for ? Is it used by end-users, DBAs or the SERVER/SYSTEM itself ??
NDF File is 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 how ever you model database has been setup, because model database is template used for every time you create new database using CREATE DATABASE Command. Unless you have added an extra data file, you will not see it. This file is used by SQL Server to store data saved by users.

Hope this information helps.

Experts please comment, if what I understand is not what Microsoft guys meant.

First of all, let’s discuss the shrinking of data files (data means mdf and ndf). You will encounter two different methods, that use the same mechanism internally: DBCC SHRINKFILE, DBCC SHRINKDATABASE.
Let me give you and example that actually happened a while ago to a customer: they were scripting a database, so we ended up having a database that was defined as having 100 GB data file (only one data file), but with no data in it. Because the customer wanted to save space, we tried to shrink the database by using DBCC SHRINKDATABASE. It had no effect whatsoever, because you cannot go lower than the initial definition of the database (100 GB in our case). By using DBCCSHRINKFILE we could actually modify the data file to a lower value, so the database size was lowered.

Second of all, for the log files (ldf). A transaction log file has a different structure and behavior compared to a data file. Every now and then, some portion of the transaction log is marked as “not needed anymore”, so it can be overwritten. This is called truncation. It simply tells SQL Server that there is space within transaction log that can be reused. If you need to recover that space and give it back to the OS, you can only do that after the log is truncated (which, in turn, relates to recovery model and whether you have taken a backup or not).

Once again, looks like I’m running into some sorts of trouble
keeping the LDF Log file under check. I was wondering if I can
automate the process by some trigger. This trigger can fire, at a specific time whenever the size of the LOG file exceeds a particular value in MB. Lets say if the size of the LOG file (.LDF) reaches 100MB, it should be auto-truncated.

Can this be achieved…?? I think so. However, I’m not much of a T-SQL Programmer. So, I’d definitely appreciate if I can get help on such a TRIGGER.

One more thing, in reference to the request above.
Is there a way we can use OSQL (SQL 2000) or SQLCMD based scripts to accomplish this task of monitoring the LOG file.

I’m planning to create a simple DOS BATch file to perform such a task. If regular DOS BAT file isn’t going to be sufficient, then probably we can think of creating a VBScript file to do the same. What do you say ??

However, my 2nd question on July 28th, 12:46am still remains ? Any idea of Automating this process of monitoring & truncating the DB LOG (.ldf) file … ? Is is possible to set some TRIGGER to monitor the LOG Size & the moment it grows beyond a certain pre-defined limit, it gets truncated & the DB is Shrunk …. ?

Dear All please i want to know how to restore database after it has been overwritten by backing up restore.My case is as follows I have restored my database from old backup by I restored this database by mistake on last data base updated so it overwritten this updated database.so please tell me how to restore my original database before it’s overwritten.

Kiran,
As long as I understand, the BAK file actually backs up the Database & not individual files. Therefore whether MDF or LDF they are the part of the same DB. Hence I doubt that would be actually possible, unless you can come across some specific utility on the internet which can just extract the MDF & not the LDF out of the BAK file.

If I were in your position, I would rather restore that BAK file on a medium where there is lot of
space available.

Hi ,
Need help urgent.
Actually i have got one file name as test.bak from my client.I donot that file they develop in sql server 2008 or sql server 2005 or 2000.
When i create .df and .ldf file when i execute to create .mdf and .ldf it is
not created these two files.
Plz anyboyd help me plz

Here’s what you need to do.
1. Ask your client whether this BAK has been created with SQL 2k, 2k5, or 2k8 ?
2. Try to RESTORE the BAK to the Database using RESTORE wizard in SQL 2k5 or 2k8.

3. I could barely understand from your broken statements, what exactly are you trying to do when u say “When i create .df and .ldf file when i execute to create .mdf and .ldf it is not created these two files.”

4. .LDF is a Transaction file & should be created once the MDF database is created & ATTACHED. Try running a few SELECT queries & you should be in business.

5. Right-click on the DB, after it is ATTACHED & check its’ PROPERTIES to see if the SQL DB engine allocated a Transactions file to the DB.

I have database that size is around 100 Mb and in which there is around 50 .ndf files. I want to reduce the number of .ndf files to 1 by transferring all data to one .ndf file. Is it possible ? If yes then how.

I try to find any article related to this on the site and on goggle but there is no lock.

hi Pinal, I am having issues writing to a mdf file in VS studio 2013 community edition. I am using this database file to store username and password in my windows forms app. I can’t seem to be able to write to it using “ExecuteNonQuery, ExecuteScalar” and other sql commands. Is there anything special about mdf files that i’m missing.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.