×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: T-SQL
Posted by: Loren Richter
Added: Sep 20, 2016 5:51 PM
Views: 15
Tags: database
  1. EXEC MASTER..xp_fixeddrives
  2. GO
  3.  
  4.  
  5.  
  6. -- SQL Server 2005 only.
  7. --
  8. -- Author: Damon T. Wilson
  9. -- Creation Date: 13-DEC-2006
  10. --
  11. -- Usage:
  12. -- Display the Database ID, Database Name, Logical File Name,
  13. -- MB Size on Disk, GB Size on Disk and Physical File Name
  14. -- for all databases in this instance.
  15. use master;
  16. go
  17.  
  18. select
  19. db.[dbid] as 'DB ID'
  20. ,db.[name] as 'Database Name'
  21. ,af.[name] as 'Logical Name'
  22. --,af.[size] as 'File Size (in 8-kilobyte (KB) pages)'
  23. --,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
  24. ,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
  25. ,af.[filename] as 'Physical Name'
  26. from sys.sysdatabases db
  27. inner join sys.sysaltfiles af
  28. on db.dbid = af.dbid
  29. where [fileid] in (1,2)
  30. order by 2;
  31.  
  32.  
  33.  
  34.  
  35. exec sp_msforeachdb 'use ? exec sp_spaceused'
  36.  
  37.  
  38. SELECT SUM(size*8192.0) AS totalsize
  39. FROM sys.master_files;
  40.  
  41.  
  42. SELECT  
  43. ds.name as filegroupname
  44. , df.name AS 'FileName'
  45. , physical_name AS 'PhysicalName'
  46. , size/128 AS 'TotalSizeinMB'
  47. , size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB'
  48. , CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
  49. , (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
  50. FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds  
  51.         ON df.data_space_id = ds.data_space_id;
  52.  
  53.  
  54.  
  55.  
  56.  
  57. ----SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
  58. ----dovs.volume_mount_point AS Drive,
  59. ----CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
  60. ----FROM sys.master_files mf
  61. ----CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
  62. ----ORDER BY FreeSpaceInMB ASC
  63. ----GO
  64.  
  65.  
  66. ----SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
  67. ----dovs.logical_volume_name AS LogicalName,
  68. ----dovs.volume_mount_point AS Drive,
  69. ----CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
  70. ----FROM sys.master_files mf
  71. ----CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
  72. ----ORDER BY FreeSpaceInMB ASC
  73. ----GO
  74.  
  75.  
  76. ----SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
  77. ----mf.physical_name PhysicalFileLocation,
  78. ----dovs.logical_volume_name AS LogicalName,
  79. ----dovs.volume_mount_point AS Drive,
  80. ----CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
  81. ----FROM sys.master_files mf
  82. ----CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
  83. ----ORDER BY FreeSpaceInMB ASC
  84. ----GO
  85.