Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- if object_id('tempdb..#drives') is not null
- drop table #drives
- SET NOCOUNT ON
- declare @ServerName sysname
- set @ServerName = @@SERVERNAME
- DECLARE @hr int
- DECLARE @fso int
- DECLARE @drive char(1)
- DECLARE @odrive int
- DECLARE @TotalSize decimal
- DECLARE @MB bigint ; SET @MB = 1048576
- if @ServerName is null or @ServerName =''
- begin
- select @ServerName = @@servername + '.master.dbo.xp_fixeddrives'
- end
- else select @ServerName = @ServerName + '.master.dbo.xp_fixeddrives'
- CREATE TABLE #drives (
- ServerName varchar(128),
- drive char(1) PRIMARY KEY,
- FreeSpace decimal(38) NULL,
- TotalSize decimal(38) NULL,
- FreespaceTimestamp DATETIME NULL)
- INSERT #drives(drive,FreeSpace)
- EXEC @ServerName
- --select * from #drives
- EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
- IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
- DECLARE dcur CURSOR LOCAL FAST_FORWARD
- FOR SELECT drive from #drives
- ORDER by drive
- OPEN dcur
- FETCH NEXT FROM dcur INTO @drive
- WHILE @@FETCH_STATUS=0
- BEGIN
- EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
- IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
- EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
- IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
- --select @TotalSize, @MB, @ServerName
- UPDATE #drives
- SET TotalSize=@TotalSize/@MB, ServerName = replace( @ServerName , '.master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
- WHERE drive=@drive
- FETCH NEXT FROM dcur INTO @drive
- END
- CLOSE dcur
- DEALLOCATE dcur
- EXEC @hr=sp_OADestroy @fso
- IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
- SELECT ServerName,
- drive,
- case when drive = 'C' then 'OS'
- when drive = 'E' then 'Sys DBs'
- when drive = 'F' then 'Data'
- when drive = 'G' then 'Log'
- when drive = 'H' then 'TempDB'
- else 'Ops' end as DiskUse,
- TotalSize as 'Total(MB)',
- cast(round(TotalSize/1024.0,0) as int) as 'Total(GB)',
- FreeSpace as 'Free(MB)',
- CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
- FreespaceTimestamp
- FROM #drives
- ORDER BY drive
- DROP TABLE #drives
- RETURN
- GO
- DECLARE
- @p1 INT = 0,
- @p2 INT = NULL,
- @p3 VARCHAR(255) = NULL,
- @p4 VARCHAR(255) = NULL
- IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
- BEGIN
- RAISERROR(15003,-1,-1, N'securityadmin')
- -- RETURN (1)
- END
- IF (@p2 IS NULL)
- EXEC sys.xp_readerrorlog @p1
- ELSE
- EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement