Progress for DBCC SHRINKFILE

Question

I have a database 21 Gb; 20 Gb of them are files (FileStream) and I have delete all files from the table but when I make a backup the backup file still 21 GB.

To solve this problem I became the idee "free the unused space".

So I'm trying to shrink my database like the following:

USE Db;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Db
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Db, 100);
GO
-- Reset the database recovery model.
ALTER DATABASE Db
SET RECOVERY FULL;
GO

SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

If I make a backup for the database after XX minutes then the backup file size is 1 Gb in this way, I can see that the unused space have been successfully cleaned. In other words the above Sql code working properly(Database after XX minutes is schrunk).

---

The problem I need to wait until this query(Shrinking operation) is finished so I'm trying to do the following:

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

I can not find any information about the SHRINKFILE command in results of the above query.

---

Did I do something wrong? why I can not see the progress of DB shrinking operation?

And my main quesiton is: How can I wait until the SHRINKFILE is finished? For example can I send from my C# code query and in the result of this query I shall get the information that the SHRINKFILE operation is finsihed or not?

Answer 1

The problem with measuring progress of DBCC SHRINKFILE is that there's not consistent way for the engine to know how much work needs to be done to shrink a file. To understand this is to know how DBCC SHRINKFILE works. Basically, the process is:

• You declare you want to shrink a file to a certain point (say 5 GB, down from 10 GB).
• The engine will start moving pages from the end of the file to the next open spot near the beginning of the file.

• The engine keeps going until A) it moves enough pages under your declared point that it can reduce the file size or B) that all the empty space is on the back end of the file.

So why does this mean that SQL Server doesn't know how much work needs to be done? Because it doesn't know how fragmented your empty space is in the file. If things are fairly well compacted and near the front of the file, the shrink file will go quickly. If not, it can take a long while. The good news is that once pages are moved within the file, they're moved. Cancelling a shrink file does not undo/rollback this work, so if you have a shrink file running for a while, then kill it before it completes, all that page movement stays intact. Meaning you can then restart the shrink file at almost the point you left off (barring any new page creation within the file).

Answer 2

I have solve the problem in a different way but this solution does not need any polling or waiting for threads and it isvery practice.

This approach will not reorganize the table indices but only remove the streaming files from the harddisk and reclaim the free space to OS.

Importent to know the below code will work on the same thread; So this will not notfiy you about the Shrink process progress but only run it on the application thread.

    var db = EFDbContext;
    try 
    {
        db.ExecuteSqlCommand(@"USE [master]

                                   ALTER DATABASE DatabaseName
                                   SET RECOVERY SIMPLE");

        db.ExecuteSqlCommand(@"USE [master]

                                   EXEC sp_filestream_force_garbage_collection 'DatabaseName'");

        db.ExecuteSqlCommand(@"USE [master]

                                   EXEC sp_filestream_force_garbage_collection 'DatabaseName'");
      }

    }
    catch (Exception e)
    {
      throw new DatabaseException(e.Message, e);
    }
    finally
    {
             db.ExecuteSqlCommand(@"USE [master]
                                    ALTER DATABASE DatabaseName
                                    SET RECOVERY FULL");
    }