USE [fxc]
--Shrink Logs
ALTER DATABASE [fxc] SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE (N'fxc_log' , 0, TRUNCATEONLY)
GO
TRUNCATE TABLE dbo.fxtb_ErrLog
GO
TRUNCATE TABLE dbo.fxtb_ActionTrail_Logs
GO
--Shrink Indexes
EXECUTE dbo.fxsp_Index_RebuildAndDeFrag "fxc";
GO
DBCC SHRINKFILE (N'fxc_xsys_index' , 0, NOTRUNCATE)
GO
DBCC SHRINKFILE (N'fxc_xsys_index' , 0, TRUNCATEONLY)
GO
DBCC SHRINKFILE (N'fxc_xuser_index' , 0, NOTRUNCATE)
GO
DBCC SHRINKFILE (N'fxc_xuser_index' , 0, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE(N'fxc' )
GO
ALTER DATABASE [fxc] SET RECOVERY FULL WITH NO_WAIT
GO
--report top 10 biggest tables
with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select TOP 10
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc