none
user db file, mdf, or ldf, won't shrink

    Question

  • I have a sql 2008 user db that I can't shrink pass the initial db file size. So, I tried to change the initial file size of the db to smaller.  I am doing it via the SSMS GUI and it won' t save the smaller size.

    How to shrink it without restarting the SQL service?  BTW, I cantn't back up ether, because the file size is too large; which is the reason I am trying to shrink it.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, December 07, 2012 3:55 PM

Answers

  • I have a sql 2008 user db that I can't shrink pass the initial db file size. So, I tried to change the initial file size of the db to smaller.  I am doing it via the SSMS GUI and it won' t save the smaller size.

    How to shrink it without restarting the SQL service?  BTW, I cantn't back up ether, because the file size is too large; which is the reason I am trying to shrink it.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Hello before shrinking did you checked how much free space exists in the MDF or LDF file(for log file please check the utilization using DBCC SQLPERF(LOGSPACE)..

    Usually shrinking an datafile is not an good choice as every one know that it can cause fragmentation.. Incase if the MDF file as enough space in it & its database mdf file dedciated its own drive ->then you dont need to worry, Incase if you are thinking to give the space back to OS then check the Database mdf used & free space in the MDF file then shrink it, If the data occupied then you cannot shrink instead you need to Purge the data first.

    For Ldf file incase it as grown heavily -consider your database full recovery model -then try to take the log backup where the space is available  so after the logbackup completion it will archives in the inactive log records from the logfile & it can make space in the file then you can shrink the log file.

    if you donot have space to take the logbackup you can switch the recovery model from full to simple(ensure after switching you have to take full backup) then shrink it.

    then do otheroperations.

    Instead of GUI you can use DBCC SHRINKFILE (T-SQL)

    http://msdn.microsoft.com/en-us//library/ms189493.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    Friday, December 07, 2012 7:55 PM

All replies

  • Hello Light_wt,

    If your prime reason for trying to shrink the files is backup then it isn’t right. Database backup backs up the data within the database. It does mirror the files.

    You cannot shrink database below actual data in the database.

    You may try to you NOTRUNCATE which move pages from end of a data file to unallocated pages in the front. This is only for data files.

    As you are using sql server 2008 then you are use backup with compression option i.e.

    BACKUP DATABASE ABC TO DISK='D:\ABC.bak' 
    WITH COMPRESSION;

    Hope that helps.

    Friday, December 07, 2012 4:34 PM
  • Please note Backup compression is available in core edition.
    Friday, December 07, 2012 4:40 PM
  • the sql edition here  doesn't have comperssion

    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, December 07, 2012 5:52 PM
  • Please review 1st part reply & please look at the actual space used.
    Friday, December 07, 2012 6:34 PM
  • Another option is to add additional files to the filegroup and spread the data across the files. This way you manage a group of smaller files that are easier to restore (or move) to different smaller drives, rather then one large file. I routinely do this for all but my smallest databases to provide more flexibility.

    Bill

     

    William F. Kinsley

    Friday, December 07, 2012 6:55 PM
  • I have a sql 2008 user db that I can't shrink pass the initial db file size. So, I tried to change the initial file size of the db to smaller.  I am doing it via the SSMS GUI and it won' t save the smaller size.

    How to shrink it without restarting the SQL service?  BTW, I cantn't back up ether, because the file size is too large; which is the reason I am trying to shrink it.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Hello before shrinking did you checked how much free space exists in the MDF or LDF file(for log file please check the utilization using DBCC SQLPERF(LOGSPACE)..

    Usually shrinking an datafile is not an good choice as every one know that it can cause fragmentation.. Incase if the MDF file as enough space in it & its database mdf file dedciated its own drive ->then you dont need to worry, Incase if you are thinking to give the space back to OS then check the Database mdf used & free space in the MDF file then shrink it, If the data occupied then you cannot shrink instead you need to Purge the data first.

    For Ldf file incase it as grown heavily -consider your database full recovery model -then try to take the log backup where the space is available  so after the logbackup completion it will archives in the inactive log records from the logfile & it can make space in the file then you can shrink the log file.

    if you donot have space to take the logbackup you can switch the recovery model from full to simple(ensure after switching you have to take full backup) then shrink it.

    then do otheroperations.

    Instead of GUI you can use DBCC SHRINKFILE (T-SQL)

    http://msdn.microsoft.com/en-us//library/ms189493.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    Friday, December 07, 2012 7:55 PM
  • To shrink backup size, you can use Third party backup or compression if enterprise, developer, DC, std edition you using or you can create SSIS package forach loop and use winzip, winrar tools as IInd step to backup to compress.

    Manish



    Another point --> take differential backup or Tlog backups to avoid large file backups.
    • Edited by LearnerSql Friday, December 07, 2012 8:13 PM
    Friday, December 07, 2012 8:08 PM
  • any progress?


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Saturday, December 08, 2012 4:55 PM