One of our drive got filled (E: drive) and we decided to shrink the data file from 1.6TB to 500GB.
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
Tried to shrink the file with "TRUNCATEONLY" option, didnt work.
DBCC SHRINKFILE (N'db_name_dat' , 0, TRUNCATEONLY)
-no space is released to OS.
--then, tried below. its running for more than 2 hours
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.