How to check the disk space of shared disk on cluster environment?

We created all user databases on shared disk Lun1 and Lun2 on drive D: (Lun1 and Lun2 were subfolder undre drive D:). If I ran xp_fixeddrives, it would only tell me how much space left on drive D:,  and didnt tell me how much free space on the shared disk Lun1 and Lun2. Right I just manually checked by check the properties of Lun1 and Lun2 to get the information. Do you have any commands or any stored procedures for monitoring the disk space on shared disk?
Thanks!
luyanAsked:
Who is Participating?
 
MohammedUConnect With a Mentor Commented:
You have to modify the proc...
Use the following...


CREATE procedure sp_checkdbspace
AS
SET nocount ON

CREATE TABLE #DriveList (
name varchar(20) null,
lowfree int null,
highfree int null,
mediatype int null
)

CREATE TABLE #DirList (
Drive varchar(20) null,
DirResults varchar(255) null
)

INSERT INTO #DriveList EXEC master.dbo.xp_availablemedia

DECLARE @Drive varchar(20),
@CMD varchar(255)

DECLARE mycursor CURSOR
FOR
SELECT name
FROM #DriveList
ORDER BY name

OPEN mycursor

FETCH mycursor INTO @Drive

IF CURSOR_STATUS('variable', '@mycursor') = 0
BEGIN
PRINT 'No such device'
CLOSE mycursor
DEALLOCATE mycursor
return
END

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CMD = 'insert into #DirList (DirResults) EXEC
master..xp_cmdshell ''DIR /-C ' + @Drive + ''''
EXEC (@CMD)
UPDATE #DirList SET Drive = @Drive WHERE Drive IS NULL
FETCH mycursor INTO @Drive
END

CLOSE mycursor
DEALLOCATE mycursor


SELECT *, BytesFree = convert(numeric(18,0),
rtrim(ltrim(substring(replace(DirResults, ' bytes free', ''), 26,
50))))
INTO #SpaceOnDisk
FROM #DirList
WHERE DirResults LIKE '%bytes free%'



select Drive, DiskMBFree = convert(numeric(10,3), BytesFree / 1048576) from #SpaceOnDisk

drop table #DriveList, #SpaceOnDisk, #DirList
0
 
MohammedUCommented:
You can try using DIR command with XP_CMDSHELL...
Check the following url...
http://bytes.com/groups/ms-sql/477715-check-diskspace-unc-via-t-sql
0
 
luyanAuthor Commented:
I could create the stored procedure sp_checkdbspace. But when I ran it, got the following error message.
Msg 208, Level 16, State 1, Procedure sp_checkdbspace, Line 52
Invalid object name 'msdb..DBSpaceHistory'.

0
 
luyanAuthor Commented:
Thanks for your script. It's good to find the free space information for every drives. How can I get the information of different Lun (such as sub-directories D:\Lun1 and D:\Lun2) of storage device through running script?
I could get the information by running diskmgnt.msc.  How to put the output into report? I would like to get email of this report.
Thanks!
0
 
luyanAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.