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
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.
Hello Pinal dev,
How can we check , wehther the data presnt or not in the files?
hi all,
how much .ndf files create in one database?
Regards,
Mangesh
Hi,
Is there any possibility of restoring a .bak file without .ldf…I mean to say .bak file containing .mdf and .ldf due to space constraint is it possible to restore only .mdf..
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,
Is there any query to find out the initial size of database when it was created?
Thanks Imran for your elaborated explanation on shrinking. Thanks lot
My Sql Server 2000 Data base Ndf file have 45 gb.Is it possible to reduce witout risk.
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
Dear TPT,
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.
6. Make sure, you have OWNER permission on the DB.
Hope this helps … !
Regards,
Aashish Vaghela
@Mariyappan …. Here’s what you’ll need to do.
USE
DBCC SHRINKDATABASE
DBCC SHRINKFILE
‘ For e.g…. D:MyLargeDBsMyCustomerDBCustomers.NDF
/* ********** Hope it helps …. ! **********
Regards,
Aashish.Vaghela
******************************************** */
Hi Pinal,
One of our customers DB growth reached to 8 GB. I removed the unwanted column which stored the 5GB of Images in a table. Now I checked the table space by using the command
sp_spaceused
it is reduced normal. But the database size still remains 8 GB not even a KB reduced.
Shall I use the following batch?
————————–Batch starts——————-
ALTER DATABASE [databasename] SET RECOVERY Simple
WITH NO_WAIT
GO
–shrink the mdf file
DBCC SHRINKFILE (N’name of the MDF file’ , 3000)
GO
ALTER DATABASE [databasename] SET RECOVERY FULL
–rebuild index…
————————–Batch ends———————
Thanks & regards,
Vijay
how to shrink in parts?
How can I shrink Transaction Log file on SQL Server 2012 Always on Instances? I tried Backup with trunkcate and dbcc shrinkfile(,size). not worked. Please help me to shrink the log file on AON.
How to shrink mdf or ndf files on primary server(Dtabase is in mirroring) in sql 2008 r2? Kindly reply me
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.
Kindly reply me .
Thanks.
Amit K Singh
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.
Thanks George
I have a question? I wanted a clean copy of my database with no production data for test and dev servers etc.. So I took a copy of the production database and Deleted all the data from the tables I did not need but the file is still 15GB. So, what should I do to get this down? Thanks
Hi Pinal,
thanks for sharing the knowledge, i have one question? recently i have one scenario that i limit the logfile(LDF) to 5GB but it grows till max and after that my website stopped working and i have following error
‘The transaction log for database ‘prod’ is full due to ‘LOG_BACKUP’
i tried so much to fix the issue and tried to increase the logfile but cant.so i switched the database from Full mode to simple and shrink the LDF file but ldf file was 28GB and this how i able to solve the issue.Please tell me in such kind of scenarios how to be on safe side and fix the issue.also please tell me if i dropped the ldf file than any data inconsistency in my database.
Regards,
Deleting/rebuilding LDF file is not a good idea at all as this will cause inconsistency in the data. Instead of switching the recovery model from full to simple, you should first identify who is holding up the data into the LDF file. This can be done by querying DMV called sys.databases. Based on the message which you mentioned it looks like your database is in full recovery model but you are not taking transaction log backups of the database. This is one of the most common cause of having huge transaction log file whereas the database file remain small. If you do not want to have point in time recovery, because you’re not taking transaction log backup, you should change the recovery model to simple. In simple recovery model you are only allowed to take full and differential backups. Transaction log backups are not allowed in simple recovery model, which are used to perform point in time recovery
Hope this helps. And sorry for the delay.
Thanks a lot Dear Pinal for the help,actually I m running Always-ON and compulsory I put my databases into full recovery mode.let me check with veeam support why logs not get truncated even check option truncate logs.
Thanks a lot,
Regards,
Abdul Rehman
Hello Team,
We have a SQL database server and the disk space becomes full due to the transaction log file which is huge as 750 GB. And the remaining space on the disk is around 20 GB, and I’m pretty sure this gonna be full at some particular point of time.
To avoid any future interruption on the transaction’s which are happening on daily basis, I need to shrink the file which will be relatively very small when done using the SQL server management studio. As to my knowledge this should be the procedure (select database -> Task -> Shrink -> Log and here what is the option I need to select Whether it is
*) Release unused space
*) Reorganized pages before releasing unused space – Because since my data is large i suppose it will take lot of time of the process to be completed and we have transaction been happened which cannot be stopped — If I provide relatively higher number say 500 GB in the space, will the process be completed faster or again it will take time.
However, while this process is ongoing will there be any transaction mismatch/lost or affect anything from user side if he does anything.
In parallel is there any impact gonna happen due to this shrinking.
However, If the process is gonna take for several hours, can we make a partition/split this file to 6 or 7 sub-files depending on the size and time elapsed?
Kind Regards,
Thomas John
hello Pinal,
I want to perform following scenario in alwayson:
take log backup of all databases which are at primary Instance in alwayson….then shrink the log files for these databases and then delete the log backup….can you help on this?
The steps you mentioned would work fine. What help do you need? Just one question – why you are deleting log backups? You don’t need them?