locked
Secondary Data File - Disk Space RRS feed

  • Question

  • I have a database backup file - the backup file itself is around 70 GB .  It's comprised of one mdf  and ndf and log file.

    The mdf and ndf combined are about double the size of the backup file itself. When the db is restored its taking up about twice the size of the backup causing space issues. 

    The backup itself is on another server , not where the restored DB exists and to do the restore I used the restore from disk with 'move' command to move the three files to the drive where the restored db lives.

    Question is whether after the restore is done can I remove the ndf file?


    Robert

    Sunday, February 16, 2014 11:49 AM

Answers

  • Question is whether after the restore is done can I remove the ndf file?

    The restored database will be exactly like the original but you can reduce the file sizes after the restore using DBCC SHRINKFILE.  Assuming the secondary file is in the PRIMARY filegroup, specify the EMPTYFILE option to move data to the remaining primary file.  Then remove the file entirely with ALTER DATABASE.

    DBCC SHRINKFILE('secondary_file_logical_name', EMPTYFILE);

    But is seems you don't really need to remove the file entirely but just reduce the size.  In that case, specify the desired target size instead of EMPTYFILE.  See http://msdn.microsoft.com/en-us/library/ms189493.aspx for more information and examples.



    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Shanky_621MVP Sunday, February 16, 2014 1:02 PM
    • Marked as answer by Sofiya Li Thursday, February 20, 2014 2:20 AM
    Sunday, February 16, 2014 12:47 PM
  • Don't have exact error message but it was something along the lines of not enough space to perform the shrink operation itself.


    Robert

    Hello,

    Some free space is required thats mandatory see below article see section  troubleshooting .You need to have free space

    http://technet.microsoft.com/en-us/library/ms189493%28v=sql.105%29.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Monday, February 17, 2014 9:34 AM
    • Marked as answer by Sofiya Li Thursday, February 20, 2014 2:20 AM
    Sunday, February 16, 2014 4:20 PM

All replies

  • Question is whether after the restore is done can I remove the ndf file?

    The restored database will be exactly like the original but you can reduce the file sizes after the restore using DBCC SHRINKFILE.  Assuming the secondary file is in the PRIMARY filegroup, specify the EMPTYFILE option to move data to the remaining primary file.  Then remove the file entirely with ALTER DATABASE.

    DBCC SHRINKFILE('secondary_file_logical_name', EMPTYFILE);

    But is seems you don't really need to remove the file entirely but just reduce the size.  In that case, specify the desired target size instead of EMPTYFILE.  See http://msdn.microsoft.com/en-us/library/ms189493.aspx for more information and examples.



    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Shanky_621MVP Sunday, February 16, 2014 1:02 PM
    • Marked as answer by Sofiya Li Thursday, February 20, 2014 2:20 AM
    Sunday, February 16, 2014 12:47 PM
  • Please not that backup file only includes data and not free spaces that is why your backup files size is less than that of original DB.As you already told even if size is half of backup when you restored it took equivalent space.You can follow method what Dan told but it seems you are worried about space.If that is case you will have to first try to reduce space from log file how much space that is taking ,what is size.can you first try to shrink log files see if you can get some space.If log file is small ultimately you might have to shrink using DBCC SHRINKFILE command but please remember shrinkiing of data file causes massive logical fragmentation.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, February 16, 2014 1:02 PM
  • Thank you

    If I use the option as you've stated above and it moves the secondary file to the primary do I not end up with the same size problem?


    Robert

    Sunday, February 16, 2014 1:05 PM
  • Tried shrinking but apparently did not have enough space even for that operation as it failed.

    I've seen other blog posts indicating that a shrink operation is a bad idea. Not sure which is correct.


    Robert

    Sunday, February 16, 2014 3:18 PM
  • I have a database backup file - the backup file itself is around 70 GB .  It's comprised of one mdf  and ndf and log file.

    The mdf and ndf combined are about double the size of the backup file itself. When the db is restored its taking up about twice the size of the backup causing space issues. 

    The backup itself is on another server , not where the restored DB exists and to do the restore I used the restore from disk with 'move' command to move the three files to the drive where the restored db lives.

    Question is whether after the restore is done can I remove the ndf file?


    Robert

    First thing first..what version of SQL is your database? If the backup is not compressed, then you may have some free space within either the db files or log files.

    In order to be able to restore the db you would need exact amount of disk space as your db files are. Once restored, if you want to get rid of the .ndf file, please try the method suggested by Dan Guzman and it should work perfectly. As it will only move the data that really exists so the free space allocated is released to the OS.

    It doesnt look like you are worried about the log file size here, so please ignore the log shrink recommendations. You only do that if you have bigger issues...

    HTH...

    Sunday, February 16, 2014 3:27 PM
  • Tried shrinking but apparently did not have enough space even for that operation as it failed.

    I've seen other blog posts indicating that a shrink operation is a bad idea. Not sure which is correct.


    Robert

    Failed ? What is error.

    Yes that is what I said Both Data and log file shrink is operation which has performance impact but Data file shrink has worst affect as it fragments indexes very much.While log file shrink release space and when it grows again to reclaim space amount of work done is costly as instant file initialization is not there for log files so getting space for log file to grow sometimes affect performance.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Monday, February 17, 2014 9:34 AM
    Sunday, February 16, 2014 3:30 PM
  • Don't have exact error message but it was something along the lines of not enough space to perform the shrink operation itself.


    Robert

    Sunday, February 16, 2014 3:50 PM
  • Don't have exact error message but it was something along the lines of not enough space to perform the shrink operation itself.


    Robert

    Hello,

    Some free space is required thats mandatory see below article see section  troubleshooting .You need to have free space

    http://technet.microsoft.com/en-us/library/ms189493%28v=sql.105%29.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Monday, February 17, 2014 9:34 AM
    • Marked as answer by Sofiya Li Thursday, February 20, 2014 2:20 AM
    Sunday, February 16, 2014 4:20 PM
  • Hi Robert,

    Try shrinking in smaller chunks. This might help you to overcome the space issue.

    Say if i have a file which is 100 GB which ca release space of 50 Gb, This is some thing i would do

    DBCC SHRINKFILE (FILE_NAME, 90000 )

    DBCC SHRINKFILE (FILE_NAME, 80000 )

    ...and so on to serve the purpose.

    Hope this helps,

    Bhanu

    Sunday, February 16, 2014 10:58 PM