/*********************************/ /**** Diskspace Report ***********/ /**** Tested: 2005, 2008, ********/ /**** 2008 R2, 2012, 2014, 2016 **/ /*********************************/ EXEC XP_FIXEDDRIVES /*********************************************/ /**** Database & Table Space Usage ***********/ /**** Tested: 2008, 2008 R2 ******************/ /**** 2012, 2014, 2016 ***************/ /*********************************************/ -- To capture the Database Space information EXEC SP_SPACEUSED; -- To capture the given table Space information SP_SPACEUSED '[SCHEMA].[TABLE]'; /*********************************************/ /**** Database space and other information ***/ /**** Tested: 2008, 2008 R2 ******************/ /**** 2012, 2014, 2016 ***************/ /*********************************************/ --Capture All databases information from the SQL instance EXEC SP_HELPDB; --Capture the given database information EXEC SP_HELPDB 'Master'; /**************************************************/ /**** Log Space Usage Report for All databases*****/ /**** Tested: 2008, 2008 R2 ***********************/ /**** 2012, 2014, 2016 ********************/ /**************************************************/ DBCC SQLPERF(LOGSPACE); /*********************************************/ /**** Drive Total and Free space info ********/ /**** Tested: 2008, 2008 R2 ******************/ /**** 2012, 2014, 2016 ***************/ /*********************************************/ SELECT [Total(GB)], [FreeSpace(GB)], CAST(([FreeSpace(GB)]*100)/[Total(GB)] AS DECIMAL(15,2)) AS [%FreeSpace] FROM (SELECT DISTINCT OVS.volume_mount_point [Drive], CAST(OVS.total_bytes / 1073741824 AS DECIMAL(15,2)) [Total(GB)], CAST(OVS.available_bytes / 1073741824 AS DECIMAL(15,2)) [FreeSpace(GB)] FROM sys.master_files SMF CROSS APPLY sys.dm_os_volume_stats(SMF.database_id, SMF.[file_id]) OVS ) AS Result; /*********************************************/ /**** Data and Log file wise Disk Usage ******/ /**** Tested: 2008, 2008 R2 ******************/ /**** 2012, 2014, 2016 ***************/ /*********************************************/ -- Data and Log file wise space usage for all databases SELECT D.name AS 'DB_Name', CASE WHEN MF.[Type] = 0 THEN 'DATA' ELSE 'LOG' END AS 'File_Type', MF.Name as 'File_Name', CAST((SUM(size)*8)/1024.00 AS DECIMAL(15,2)) AS [Disk_Space(MB)], CAST((SUM(size)*8)/(1024.00 * 1024.00) AS DECIMAL(15,2)) AS [Disk_Space(GB)] FROM sys.databases D JOIN sys.master_files MF ON D.database_id=MF.database_id GROUP BY D.name,MF.Name,MF.Type ORDER BY D.name,MF.Name,MF.Type; -- Database wise total disk usage in a given instance SELECT D.name AS 'DB_Name', CAST((SUM(size)*8)/1024.00 AS DECIMAL(15,2)) AS [Total_Disk_Space(MB)], CAST((SUM(size)*8)/(1024.00 * 1024.00) AS DECIMAL(15,2)) AS [Total_Disk_Space(GB)] FROM sys.databases D JOIN sys.master_files MF ON D.database_id=MF.database_id GROUP BY D.name ORDER BY D.name; /**************************************************/ /**** For a given database used and free space ****/ /**** Tested: 2008, 2008 R2 ***********************/ /**** 2012, 2014, 2016 ********************/ /**************************************************/ SELECT [Database], [File_Name], [FileGroup_Name], File_Path, [Total_Space(MB)], [Available_Space(MB)], CAST([Total_Space(MB)]/1024 AS DECIMAL(15,2)) AS [Total_Space(GB)], CAST([Available_Space(MB)]/1024 AS DECIMAL(15,2)) AS [Available_Space(GB)], CAST(([Available_Space(MB)]*100)/[Total_Space(MB)] AS DECIMAL(15,2)) AS '%_Free' FROM (SELECT DB_NAME() AS 'Database', df.name AS [File_Name], ds.name AS [FileGroup_Name], df.physical_name AS [File_Path], CAST((df.size/128.0) AS DECIMAL(15,2)) AS [Total_Space(MB)], CAST(df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Available_Space(MB)] FROM sys.database_files AS df WITH (NOLOCK) LEFT OUTER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON df.data_space_id = ds.data_space_id) AS Result; /**************************************************/ /**** Table wise Disk Space Report ****************/ /**** Tested: 2008, 2008 R2 ***********************/ /**** 2012, 2014, 2016 ********************/ /**************************************************/ SET NOCOUNT ON; BEGIN TRY --Create a temparory table CREATE TABLE #Tab ( [Name] NVARCHAR(128), [Rows] CHAR(11), [Reserved] VARCHAR(18), [Data] VARCHAR(18), [Index_size] VARCHAR(18), [Unused] VARCHAR(18)); --Capture all tables data allocation information INSERT #Tab EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' ; --Alter Rows column datatype to BIGINT to get the result in sorted order ALTER TABLE #Tab ALTER COLUMN [ROWS] BIGINT ; -- Get the final result: Remove KB and convert it into MB SELECT Name, [Rows], CAST(LTRIM(RTRIM(REPLACE(Reserved,'KB',''))) AS BIGINT)/1024.0 AS 'Reserved MB', CAST(LTRIM(RTRIM(REPLACE(Data,'KB',''))) AS BIGINT)/1024.0 AS 'Data MB', CAST(LTRIM(RTRIM(REPLACE(Index_Size,'KB',''))) AS BIGINT)/1024.0 AS 'Index_Size MB', CAST(LTRIM(RTRIM(REPLACE(Unused,'KB',''))) AS BIGINT)/1024.0 AS 'Unused MB' FROM #Tab ORDER BY [rows] DESC; END TRY BEGIN CATCH DROP TABLE #Tab; END CATCH -- Drop the temparory table DROP TABLE #Tab; /***************************************/ /***********XP_CMDSHELL ****************/ /***************************************/ EXEC xp_cmdshell 'wmic logicaldisk get size,freespace,caption';