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_helpfileLet 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)
GOFollowing 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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
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!
how to purge data older than 24 months in sql server 2008 R2 database
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.