This month's discount Comprehensive Database Performance Health Check Testimonials
SQLAuthority with Pinal Dave
Home

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

Pinal Dave
SQL Performance
58 Comments

Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you. The question was about shrinking NDF and MDF files and whether it loses data.

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 MDF files?? 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.
Following is the script to shrink whole database.
DBCC SHRINKDATABASE (dbName)

Following is the script to shrink single file.
DBCC SHRINKFILE (logicalLogFileName)

To find logicalLogFileName following command has to be ran.
USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
DBCC SHRINKDATABASE (AdventureWorks)
GO
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
GO
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
GO
Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.
Displaying AdventureWorks database files.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.

Discover more from SQL Authority with Pinal Dave

Subscribe to get the latest posts sent to your email.

SQL Data Storage, SQL Scripts, SQL Server DBCC, SQL Server Security
Previous Post
SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2
Next Post
Operator Cost Percentages Are Estimates: Read Actual Time Instead

Related Posts

58 Comments. Leave new

  • Nipam
    April 30, 2017 11:01 am

    Hi Pinal,

    I was checking shrinking mdf file.
    Found tons of article advising not to shrink.
    As that will create problem with the indexes.

    Can you please share some suggestions ?

    Thanks!

    Reply
  • umesh1987
    May 24, 2017 12:59 pm

    how to purge data older than 24 months in sql server 2008 R2 database

    Reply
  • Mehdi Hosseinpour
    July 28, 2020 4:01 pm

    Hello, and I hope you help me
    I have .ndf file that this file is too large, 45GB. We have not many data in this database. I shrink db and ndf file, but the size does not change.

    Reply
« Older Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

SQL Server slow? Let's fix it together in about four hours.

Pinal Dave

In my Comprehensive Database Performance Health Check, you share your screen, I guide you, and we fix your biggest performance troublemakers. No passwords. No remote access.

You keep every script, and the know-how to fix it yourself next time.

Want your whole team to learn it?

My Practical Workshop has no PowerPoint, just live demos. My AI Enablement Workshop shows your team where AI saves hours, and where it quietly costs them.

Over 400 customers have worked with me this way, and not one shared a password. See all consulting options.

Who We Are

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 24 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 19 books and 125 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Shaivi Pinalkumar Dave is a book author and a women’s artistic gymnast.