DBCC SHRINKFILE shrinks the size of database/log file of the current database either to the specific size or as small as it can.
Below query gives you the name of the database/log file, Total size(MB), Used Space(MB), %Used, Physical location of file and the Shrink SQL statement with the specific size which you can execute on the database to shrink the respective file:
SELECT file_id ,name ,size_mb=size/128.0 ,UsedSpace_MB=fileproperty(name, 'spaceused')/128.0 ,UsedSpace_Pct = CAST(fileproperty(name, 'spaceused')*1./size as decimal(5,2)) * 100 ,physical_name ,shrink_stmt='dbcc shrinkfile(' + CAST(file_id as varchar) + ',' + CAST((fileproperty(name, 'spaceused')/128 + 1) as varchar) + ')' FROM sys.database_files
Below query gives you the name of the database/log file, Total size(MB), Used Space(MB), %Used, Physical location of file and the Shrink SQL statement with the specific size which you can execute on the database to shrink the respective file:
SELECT file_id ,name ,size_mb=size/128.0 ,UsedSpace_MB=fileproperty(name, 'spaceused')/128.0 ,UsedSpace_Pct = CAST(fileproperty(name, 'spaceused')*1./size as decimal(5,2)) * 100 ,physical_name ,shrink_stmt='dbcc shrinkfile(' + CAST(file_id as varchar) + ',' + CAST((fileproperty(name, 'spaceused')/128 + 1) as varchar) + ')' FROM sys.database_files
