locked
proper way to stop dbcc shrinkfile? RRS feed

  • Question

  • According to Microsoft's documentation the dbcc shrinkfile process can be stopped at any time without impact, and resumed at a later time if necessary.  I have some shrinkfile processes that will take up to days to complete (migrating data from old SAN to new SAN without downtime).  In the event I need to stop the shrinkfile process, what is the correct way to do this without any risk of data corruption?  Do I just hard kill the process or is there a better way?

    Here is a statement from TechNet, but it makes no mention of how to stop it:

    "DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained."

    Thursday, October 10, 2013 1:11 PM

All replies

  • Hello,

    How do you start the DBCC Shrinkfile command?
    - In SSMS query windows? Then simply click on "Stop" button.
    - AS SQL Server-Agent Job? Then stop the job.

    BTW, you could shrink the files also "chunk by chunk", means always only for a predefined size, e.g. 500 MB per run:

    DECLARE @DataFileName VARCHAR(255);  
    SET @DataFileName = (SELECT name FROM sysfiles WHERE groupid = 1);
    
    DECLARE @TargetSize INT; 
    -- Select current size and substract 500 MB
    SET @TargetSize = ROUND(8 * (SELECT size FROM sysfiles WHERE groupid = 1) / 1024, 0) - 500;
    
    EXEC ('DBCC SHRINKFILE (' + @DataFileName + ', ' + @TargetSize + ')');

    This reduces the runtime & IO workload; re-execute it until you reaches the wanted size.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, October 10, 2013 1:21 PM
  • No corruption. Just press cancel the query. Moreover, do you know that DBCC SHRINKFILE has a parameter named 

    TRUNCATEONLY?


    It does release the space at the end of the file you are shrinking BUT does not perform many data page movement

    Thus it should take less time...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Kalman Toth Friday, October 11, 2013 5:19 AM
    Friday, October 11, 2013 2:50 AM
    Answerer
  • You can stop the query or You can kill the SPID.

    Kill SPID 

    Friday, October 11, 2013 2:54 AM
  • HI

    When performing large shrink operations on a large database I tend to process the shrink task incrementally. i.e. shrink the file in bit size chunks (i.e. 512mb). I usually run this operation from the job agent and log the operation to a text file. The following bit of code should do the trick.  

    DECLARE @StartSize INT 
    DECLARE @TargetSize INT
    
    SET   @StartSize  = -- SET START SIZE OF THE DATABASE FILE (MB)
    Set   @TargetSize = -- SET END SIZE OF THE DATABASE FILE (MB)
    
    WHILE @StartSize > @TargetSize
    BEGIN
    SET @StartSize = @StartSize - 512
        DBCC SHRINKFILE (N'file name' , @StartSize)
    END
    GO
    

     

     

     

    • Proposed as answer by Anil_Kumar_DBA Monday, November 21, 2016 6:12 AM
    Friday, October 11, 2013 9:03 AM