none
Unable to shrink Data file sql server 2008

    Question

  • Unable to shrink datafile. tried dbcc shrink commands no luck.I still see huge unused space.The databse is setup under Transactional Replication.On the other hand the replicated database is already shrunk .Any ideas?
    Tuesday, January 04, 2011 10:09 PM

Answers

  • Hi,

    If you use TRUNCATEONLY option, it will release all free space at the end of the file to the operating system but does not perform any page movement inside the file and target_size is ignored if specified with TRUNCATEONLY. So that the data file may not be shunk too much since no page movement. Please try the following T-SQL statement to shrink the data file to a specify target size:

    USE <database_name>;
    GO
    DBCC SHRINKFILE (<file_name or file_id>, <target_size_in_MB>);
    GO
    
    

    For more information, please see DBCC SHRINKFILE (http://msdn.microsoft.com/en-us/library/ms189493.aspx).

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, January 06, 2011 7:15 AM

All replies

  • When you say unable what exactly do you mean? i.e. do you get errors or does it timeout? If you try shrinking by only a small amount does that work? Can you see if you are being blocked? Also are you trying to shrink the log or data files?

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005), MCAD, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Wednesday, January 05, 2011 6:19 AM
  • Hi

    Have you tried DBCC SHRINKFILE with TRUNCATEONLY option?

    http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 05, 2011 7:54 AM
  • It doesnt time out or error out.I tried doing it in small chunks  but no luck.Its not blocked either.I get the result immedietly.Im trying to shrink the data files. The data data files Im trying to shrink belongs to SQL Partition filegroup.
    Wednesday, January 05, 2011 12:10 PM
  • Yes I tried the TRUNCATEONLY  option only. Doesnt work.
    Wednesday, January 05, 2011 12:11 PM
  • I hope  you do understand why it "does not work" even with TRUNCATEONLY option
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 05, 2011 12:14 PM
  • Sorry I dont understand.Please explain me.
    Wednesday, January 05, 2011 2:21 PM
  • Hi,

    If you use TRUNCATEONLY option, it will release all free space at the end of the file to the operating system but does not perform any page movement inside the file and target_size is ignored if specified with TRUNCATEONLY. So that the data file may not be shunk too much since no page movement. Please try the following T-SQL statement to shrink the data file to a specify target size:

    USE <database_name>;
    GO
    DBCC SHRINKFILE (<file_name or file_id>, <target_size_in_MB>);
    GO
    
    

    For more information, please see DBCC SHRINKFILE (http://msdn.microsoft.com/en-us/library/ms189493.aspx).

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, January 06, 2011 7:15 AM
  • Yep, in addition, there is enough that at least one page at the end has a data TRUNCATEONLY won't be usefull
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 06, 2011 7:45 AM
  • During database maintenance period take database offline, then online.

    Use Object Explorer GUI shrink file to shrink each file in the database. Image follows:

    Log shrinking link: http://sqlusa.com/bestpractices2005/shrinklog/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, January 07, 2011 10:05 AM