shrinking a large data file

Samantha r 1,066 Reputation points

Hi All,

One of our drive got filled (E: drive) and we decided to shrink the data file from 1.6TB to 500GB.

E drive

Below screenshot shows free space left inside the data file.


--DB_NAME() AS DbName,

name AS FileName,


CAST((size/128.0)/1024./1024. AS NUMERIC(18,2)) AS CurrentSizeTB,

CAST((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)/1024. AS NUMERIC(18,2)) AS FreeSpaceGB

FROM sys.database_files;



Tried to shrink the file with "TRUNCATEONLY" option, didnt work.

USE [dbname]




-no space is released to OS.

--then, tried below. its running for more than 2 hours

USE [dbname]


DBCC SHRINKFILE (N'db_name_dat', 512050)


Am I missing anything, what is the best way to shrink or release free space to OS? Previously, we used to have a log table which has taken up all the data and we truncated the table hoping shrink file will release free space to OS but its not. Note: The logtable has LOB columns defined on it.

Please let me know if you have any other ideas to release some space to OS. Do let me know in case if you need any information from me. This exercise we are doing it in our QA env. During the weekend, we have to do it on prod. In Prod, one variable is, the database is part of AG.



SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,026 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 88,856 Reputation points

    You truncated the table, but have you also take measures to keep down the size of the log table in the future, like adding a cleanup job? Else there is not much point in shrinking the file.

    When there are LOB columns involved, DBCC SHRINKFILE can be hugely inefficient. Although if you ran TRUNCATE TABLE, that issue may be gone.

    Active transactions should not matter that much, since SHRINK works with small transactions. Still it is better to do the shrinking in a maintenance window to avoid taking resources from the production workload.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Zahid Butt 456 Reputation points


    Recovery Model for the database should be 'simple'.

    Right click on database name->Options ..on the right hand side you can see recovery mode.

    Secondly you may execute below commands before shrink:



  2. Rahul Randive 4,461 Reputation points Microsoft Employee

    Hi @Samantha r

    Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system. 

    You can decide on reclaiming the unused space in your databases depending on the type of growth you have observed.

    If you feel your database size grows again then the free space is required, if you feel this much free space is not required you can go for shrink operation.

    please plan shrinking activity during a maintenance window as it might cause blocking during business hours.

    Also note, it is not recommended to shrink data files frequently as it can cause fragmentation and affect performance. 

    Most databases require some available free space for regular day-to-day operations. If the shrink process reclaims unused allocated space that will again be shortly reused as part of the customers regular workload then there is no need to shrink the database. 

    Article for your reference: 

    During shrink operation, check there should be no active transaction running on the DB.

    run basic sp_who2 active on every run to check if there are any active transactions.

    Try to decrease the space of allocation for the DB.

    Repeat this process to perform incremental shrinks.

    A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. Therefore after data files are shrunk, the indexes can become fragmented. If performance issues occur, consider rebuilding the indexes once more.

    Here is similar thread where the database is part of AG-

    Thank you!

    0 comments No comments

  3. CosmogHong-MSFT 15,211 Reputation points Microsoft Vendor

    Hi @Samantha r

    When planning to shrink data files, consider the following:

    (1) First of all, we must understand the current usage of data files.

    It is unlikely that the size of the shrinkage will exceed the size of the free space of the current file. If you want to reduce the size of the database, you must first make sure that there is indeed unused space in the data file. If the space is in use, first identify the objects (tables or indexes) that are taking up a lot of space. Then by archiving historical data, the space is freed up first.

    (2) The primary file cannot be emptied. Only auxiliary data files can be completely emptied.

    (3) If you want to empty a filegroup entirely, delete the objects (tables or indexes) assigned to the filegroup or move them to another filegroup. DBCC SHRINKFILE won't do this for you.

    After clearing the data and objects in the data file and confirming that the data file (group) has enough free space, the administrator can run the DBCC SHRINKFILE command to shrink or empty the specified file. If you want to shrink the file, fill in the required target_size, if you want to empty the file, select EMPTYFILE. When SQL Server does DBCC SHRINKFILE, it scans data files and locks the pages being read, so the performance of the database will be affected. But this is not an exclusive act. When doing shrinkfile, other users can still read and write to the database. Therefore, there is no need to arrange special server downtime to do it, and it can generally be carried out during the database maintenance period. The DBCC SHRINKFILE operation can be stopped at any point in the process and any completed work is retained. If the operation is not completed within the stipulated time, it can also be safely stopped.

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Samantha r 1,066 Reputation points

    Thank you all for the valuable inputs.

    0 comments No comments