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

SQL Server - Get filegroup and files space details

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))
[Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),
ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))
AS [Space Used (MB)],
CONVERT (Decimal(15,2),
ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))
AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname

SQL Server - Get rows and space details for all tables in database

with tempspace as (
SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows)

select SchemaName, TableName, 
sum(RowCounts) as RowsCount, 
sum(TotalSpaceKB)/1024/1024 as TotalGB, 
sum(UsedSpaceKB)/1024/1024 as UsedGB, 
sum(UnusedSpaceKB)/1024/1024 as UnusedGB
from tempspace 
group by SchemaName, TableName
order by TotalGB desc, SchemaName, TableName

SQL Server 2012 - Truncate table partitions

1. Create staging table [staging_TABLE_NAME]. The best way to create it is from SSMS 

ServerName -> Databases -> DatabaseName -> Tables -> TABLE_NAME (Right click) -> Storage -> Manage partition -> Create a staging table for partition switching

In the generated script comment ALTER TABLE lines with constraint on partition limits.

Execute script to create staging table.

Update in truncate partition script staging_TABLE_NAME with generated staging table name.

2. Get details about table partititons

DECLARE @TableName NVARCHAR(200) = N'TABLE_NAME'

SELECT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object]
     , p.partition_number AS [p#]
     , fg.name AS [filegroup]
     , p.rows
     , au.total_pages AS pages
     , CASE boundary_value_on_right
       WHEN 1 THEN 'less than'
       ELSE 'less than or equal to' END as comparison
     , rv.value
     , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
       SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
       CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
       SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
       SUBSTRING (au.first_page, 1, 1))) AS first_page
FROM sys.partitions p
INNER JOIN sys.indexes i
     ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
     ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au
     ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
     ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f
     ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
     ON dds.partition_scheme_id = ps.data_space_id
     AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
     ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv
     ON f.function_id = rv.function_id
     AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
     AND o.object_id = OBJECT_ID(@TableName)
order by 2;

3. Execute truncate script for selected partitions. To execute also the commands un-comment EXEC lines. Test it before execution in ptoduction like environment !!!!

DECLARE @partno nvarchar(5)
DECLARE @cmd1 nvarchar(200)
DECLARE @cmd2 nvarchar(200)
SELECT @cmd2 = 'TRUNCATE TABLE [staging_TABLE_NAME]'

DECLARE table_names CURSOR FOR
SELECT distinct partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number between 20 and 100
order by partition_number

OPEN table_names
FETCH NEXT FROM table_names INTO @partno
WHILE @@fetch_status = 0
BEGIN

IF EXISTS (SELECT partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number = @partno)

BEGIN

SELECT @cmd1 = 'ALTER TABLE [DBNAME].[dbo].[TABLE_NAME] SWITCH PARTITION ' + @partno + ' TO [DBNAME].[dbo].[staging_TABLE_NAME]'
   
PRINT @cmd1
PRINT @cmd2
-- EXEC sp_executeSQL @cmd1;
-- EXEC sp_executeSQL @cmd2;
END

FETCH NEXT FROM table_names INTO @partno
END

CLOSE table_names
DEALLOCATE table_names

SQL Server - Get table partitions details

SELECT    
     p.partition_id                             as PartitionID
    ,t.name                                     as [Table]
    ,ps.name                                    as PartitionScheme
    ,pf.name                                    as PartitionFunction
    ,p.partition_number                         as [Partition]
    ,p.rows                                     as [Rows]
    ,prv.value                                  as Boundary
    --,pf.type_desc                               as BoundaryType
    ,case when pf.boundary_value_on_right = 1
        then 'Right'
        else 'Left'
        end                                     as BoundarySide

FROM
    sys.tables t
    inner join sys.partitions p
        on t.object_id = p.object_id
        and p.index_id = 1
    inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
    inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id
    inner join sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
    inner join sys.partition_functions pf on ps.function_id = pf.function_id
    left outer join sys.partition_range_values prv
        on pf.function_id = prv.function_id
        and p.partition_number = prv.boundary_id
WHERE
    is_ms_shipped = 0 and
    t.name = 'TABLE_NAME'  
    and exists (select 1 from sys.partitions p2
                where t.object_id = p2.object_id
                and partition_number > 1)
order by
     t.name
    ,p.partition_number desc