SQL SERVER – Killing DBCC SHRINKFILE Process – Is it Safe?

As a SQL Server Performance Tuning consultant, I believe my life is on the roller coaster often. Recently during the Comprehensive Database Performance Health Check, I found one of my clients regularly shrinking the database files by running DBCC SHRINKFILE process. Shrinking the data file is a very bad thing and you can read it about here: SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance. Now let us learn about if Killing DBCC SHRINKFILE Process is safe or not?

Real-World Story – DBCC SHRINKFILE Process

Well, here is what had actually happened. During the consulting engagement, the client complained about poor performance for one particular database. After doing deeper investigation we were able to restore the database performance to an optimal state. However, after a while once again we received some complaints from the end-users about the poor performance.

When I immediately checked it, I found out that for one database someone was running the DBCC SHRINKFILE process for the data file (MDF file). As the database was very huge the history of running that process was of hours. The DBA who was in charge of the performance had no idea about this process running behind the scene. He was equally surprised as me and wanted to kill the process which was running the DBCC Shrinkfile on the data file.

Now here comes the million-dollar question –

Is killing the DBCC SHRINKFILE process is a safe operation or can it create troubles (corruption, rollback, unresponsive server etc)?

The answer is – yes it is a safe operation. You can kill any DBCC SHRINKFILE process with the help of the KILL spid command. I personally have not come across even a single instance where killing this operation has created a problem for the database. The matter of fact, this process moves a very few pages at a time (if memory serves right 32 pages), and there are no rollback operations as well. This means it is instant. Read more about this topic over here: SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation.

However, there is a good chance that you may find your SQL Server performance very poor during the DBCC SHRINKFILE operation or after it is completed or hosted, that is not due to killing that operation but it is because of the shrinking process itself. You can read more about this over here: SQL SERVER – Shrinking Database NDF and MDF Files – Readers’ Opinion.

Reference: Pinal Dave (https://blog.sqlauthority.com)