SQL Server - Free space management

Check free space on server partititons

EXEC MASTER..xp_fixeddrives
GO


SELECT DISTINCT dovs.logical_volume_name AS LogicalDriveName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceMB ASC

GO

Check free space in database files

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files with (NOLOCK);


Shrink database files in small chunks

DECLARE @StartSize INT 
DECLARE @TargetSize INT

SET   @StartSize  = -- SET START SIZE OF THE DATABASE FILE (MB)
Set   @TargetSize = -- SET END SIZE OF THE DATABASE FILE (MB)

WHILE @StartSize > @TargetSize
BEGIN
SET @StartSize = @StartSize - 512
    DBCC SHRINKFILE (N'file name' , @StartSize)
END

GO

View shrink operation details


SELECT 
    d.name,
    percent_complete, 
    session_id,
    start_time, 
    status, 
    command, 
    estimated_completion_time, 
    cpu_time, 
    total_elapsed_time
FROM 
    sys.dm_exec_requests E left join
    sys.databases D on e.database_id = d.database_id
WHERE
    command in ('DbccFilesCompact','DbccSpaceReclaim')



select DST.text, DMR.Status, DMR.Command, DatabaseName = db_name(R.database_id), DMR.cpu_time, R.total_elapsed_time, DMR.percent_complete
from sys.dm_exec_requests DMR

cross apply sys.dm_exec_sql_text(DMR.sql_handle) DST