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 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.
SQL SERVER - Shrinking NDF and MDF Files - A Safe Operation shrinkdb

Reference : Pinal Dave (https://blog.sqlauthority.com)
, , ,
Previous Post
SQLAuthority News – Download Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
Next Post
SQLAuthority News – Happy 60th Republic Day to India – Database Tip

Related Posts

58 Comments. Leave new

  • 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
  • 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

Leave a Reply

Menu