p_get_drive_details
This procedure can be used to pull disk details from a remote instance.
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[p_get_drive_details]
@RemoteInstance nvarchar(200)
AS
BEGIN
SET NOCOUNT ON
DECLARE @LocalQuery nvarchar(2000)
DECLARE @RemoteQuery nvarchar(2000)
DECLARE @PrintText nvarchar(100)
DECLARE @servername nvarchar(100)
DECLARE @totalbytes bigint
DECLARE @drive varchar(10)
DECLARE @FreeMB bigint
DECLARE @TotalMB bigint
– Declare temp tables
CREATE TABLE #totalSpace(line varchar(2000))
CREATE TABLE #freeSpace(drive varchar(10), FreeMB bigint)
SET @RemoteQuery = ‘SET NOCOUNT ON; SET FMTONLY OFF; exec master..xp_fixeddrives’
SET @LocalQuery = ‘
INSERT INTO #freeSpace SELECT * FROM OPENROWSET(‘‘SQLNCLI’‘, ‘‘Server=’ + @RemoteInstance + ‘;Trusted_Connection=yes;’‘,’” + @RemoteQuery + ”‘); ‘
EXEC master..sp_executesql @LocalQuery
DECLARE SpaceCursor CURSOR FOR
SELECT * FROM #freeSpace WHERE drive!=‘C’
OPEN SpaceCursor
FETCH NEXT FROM SpaceCursor INTO @drive, @FreeMB
WHILE @@FETCH_STATUS = 0
BEGIN
– Get Total Space on Drive
SET @RemoteQuery = ‘SET NOCOUNT ON; SET FMTONLY OFF; exec xp_cmdshell ‘”‘fsutil volume diskfree ‘+@drive+‘:’””
SET @LocalQuery = ‘
INSERT INTO #totalSpace SELECT * FROM
OPENROWSET(‘‘SQLNCLI’‘, ‘‘Server=’ + @RemoteInstance + ‘;Trusted_Connection=yes;’‘,’” + @RemoteQuery + ”‘); ‘
EXEC master..sp_executesql @LocalQuery
SELECT @totalbytes=rtrim(substring(line,32,100)) FROM #totalSpace WHERE line LIKE ‘Total # of bytes%’
DELETE FROM #totalSpace
– Consolidate Free space and Total Space
INSERT INTO drive_details VALUES(getdate(), @RemoteInstance, @drive, (@totalbytes/1048576), @FreeMB)
FETCH NEXT FROM SpaceCursor INTO @drive, @FreeMB
END
SET @PrintText = (SELECT count(*) FROM #freeSpace WHERE drive!=‘C’)
PRINT ‘Pulled data for ‘ + @PrintText + ‘ drives’
– Cleanup
DROP TABLE #totalSpace
DROP TABLE #freeSpace
CLOSE SpaceCursor
DEALLOCATE SpaceCursor
END
