Advertisement
Guest User

Untitled

a guest
Oct 23rd, 2014
124
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.57 KB | None | 0 0
  1. if object_id('tempdb..#drives') is not null
  2. drop table #drives
  3.  
  4. SET NOCOUNT ON
  5. declare @ServerName sysname
  6. set @ServerName = @@SERVERNAME
  7. DECLARE @hr int
  8. DECLARE @fso int
  9. DECLARE @drive char(1)
  10. DECLARE @odrive int
  11. DECLARE @TotalSize decimal
  12. DECLARE @MB bigint ; SET @MB = 1048576
  13. if @ServerName is null or @ServerName =''
  14. begin
  15. select @ServerName = @@servername + '.master.dbo.xp_fixeddrives'
  16. end
  17. else select @ServerName = @ServerName + '.master.dbo.xp_fixeddrives'
  18.  
  19. CREATE TABLE #drives (
  20. ServerName varchar(128),
  21. drive char(1) PRIMARY KEY,
  22. FreeSpace decimal(38) NULL,
  23. TotalSize decimal(38) NULL,
  24. FreespaceTimestamp DATETIME NULL)
  25. INSERT #drives(drive,FreeSpace)
  26. EXEC @ServerName
  27. --select * from #drives
  28. EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
  29. IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
  30. DECLARE dcur CURSOR LOCAL FAST_FORWARD
  31. FOR SELECT drive from #drives
  32. ORDER by drive
  33. OPEN dcur
  34. FETCH NEXT FROM dcur INTO @drive
  35. WHILE @@FETCH_STATUS=0
  36. BEGIN
  37. EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
  38. IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
  39. EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
  40. IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
  41. --select @TotalSize, @MB, @ServerName
  42. UPDATE #drives
  43. SET TotalSize=@TotalSize/@MB, ServerName = replace( @ServerName , '.master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
  44. WHERE drive=@drive
  45. FETCH NEXT FROM dcur INTO @drive
  46. END
  47. CLOSE dcur
  48. DEALLOCATE dcur
  49. EXEC @hr=sp_OADestroy @fso
  50. IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
  51. SELECT ServerName,
  52. drive,
  53. case when drive = 'C' then 'OS'
  54. when drive = 'E' then 'Sys DBs'
  55. when drive = 'F' then 'Data'
  56. when drive = 'G' then 'Log'
  57. when drive = 'H' then 'TempDB'
  58. else 'Ops' end as DiskUse,
  59. TotalSize as 'Total(MB)',
  60. cast(round(TotalSize/1024.0,0) as int) as 'Total(GB)',
  61. FreeSpace as 'Free(MB)',
  62. CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
  63. FreespaceTimestamp
  64. FROM #drives
  65. ORDER BY drive
  66. DROP TABLE #drives
  67. RETURN
  68. GO
  69.  
  70. DECLARE
  71. @p1 INT = 0,
  72. @p2 INT = NULL,
  73. @p3 VARCHAR(255) = NULL,
  74. @p4 VARCHAR(255) = NULL
  75.  
  76. IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
  77. BEGIN
  78. RAISERROR(15003,-1,-1, N'securityadmin')
  79. -- RETURN (1)
  80. END
  81.  
  82. IF (@p2 IS NULL)
  83. EXEC sys.xp_readerrorlog @p1
  84. ELSE
  85. EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement