note to self: SQL Disk summary

Script to show current Disk space free, used, by drive. And then a summary break down of DB files, folders and space used.


SET NOCOUNT ON
EXEC master.dbo.xp_cmdshell 'wmic LOGICALDISK LIST BRIEF';

IF OBJECT_ID('tempDB..##tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles
CREATE TABLE ##tmpDatabaseFiles
    (
 [DBName] [sys].[sysname]
   ,  [file_id] [int]
   , [file_guid] [uniqueidentifier]
   , [type] [tinyint]
   , [type_desc] [nvarchar](60)
   , [data_space_id] [int]
   , [name] [sys].[sysname]
   , [physical_name] [nvarchar](260)
   , [state] [tinyint]
   , [state_desc] [nvarchar](60)
   , [size] [int]
   , [max_size] [int]
   , [growth] [int]
   , [is_media_read_only] [bit]
   , [is_read_only] [bit]
   , [is_sparse] [bit]
   , [is_percent_growth] [bit]
   , [is_name_reserved] [bit]
   , [create_lsn] [numeric](25, 0)
   , [drop_lsn] [numeric](25, 0)
   , [read_only_lsn] [numeric](25, 0)
   , [read_write_lsn] [numeric](25, 0)
   , [differential_base_lsn] [numeric](25, 0)
   , [differential_base_guid] [uniqueidentifier]
   , [differential_base_time] [datetime]
   , [redo_start_lsn] [numeric](25, 0)
   , [redo_start_fork_guid] [uniqueidentifier]
   , [redo_target_lsn] [numeric](25, 0)
   , [redo_target_fork_guid] [uniqueidentifier]
   , [backup_lsn] [numeric](25, 0)
   , [CreationDate] [datetime])
 
 EXECUTE sp_msforeachdb 'INSERT INTO ##tmpDatabaseFiles SELECT ''[?]'', *, GETDATE() FROM [?].sys.database_files'
  
SELECT COUNT(DISTINCT dbname) NonSystemDBCount from ##tmpDatabaseFiles WHERE dbname NOT IN ('[master]','[model]','[tempdb]', '[msdb]')

 ;WITH CTE AS (
 SELECT
 DISTINCT
   name
 , LEFT(physical_name, LEN(physical_name)-CHARINDEX ('\', REVERSE(physical_name)) ) Folder
 ,(size * 8)/1024.0 size
 ,type_desc
  FROM ##tmpDatabaseFiles 
)
SELECT 
 LEFT(Folder,60) Folder
 ,LEFT(LTRIM(STR(COUNT(Folder))),5) Files
 ,LEFT(LTRIM(STR(SUM(size)/1024.0)),10) [GB TotalSize]
 ,LEFT(type_desc,10) FileType
FROM CTE
GROUP BY Folder,type_desc
ORDER BY Folder

IF OBJECT_ID('tempDB..#tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles



Comments :

0 comments to “note to self: SQL Disk summary”