SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding out Total Disk Space in TSQL


Finding out Total Disk Space in TSQL

Author
Message
BalasundaramRangasamy
BalasundaramRangasamy
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 184
Nice Query Raymond. Thanks
BalasundaramRangasamy
BalasundaramRangasamy
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 184
Nice Query Raymond. Thanks
steve.gouin
steve.gouin
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 20
Raymond,

the problem is that there are invalid charaters in the string to convert. Just replace the following code :

SELECT drive,
ltrim(rtrim(left(info,29))) as InfoType,
ltrim(rtrim(substring (info, charindex (':',info) + 2, 20))) as Size_Bytes
INTO #tmpFSutilDriveSpaceInfo_Fixed
FROM #tmpFSutilDriveSpaceInfo

with this one :

SELECT drive,
ltrim(rtrim(left(info,29))) as InfoType,
replace(replace(replace(substring (info, charindex (':',info) + 2, 20),' ',''),char(13),''),char(10),'') as Size_Bytes
INTO #tmpFSutilDriveSpaceInfo_Fixed
FROM #tmpFSutilDriveSpaceInfo

and everything should works fine.
harpreets.singh
harpreets.singh
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 76
Hi Guys,

I tired to use powershell to get drive space info.

Check this blog http://myharpreet.blogspot.com/2012/01/t-sql-to-get-drive-space-info.html

Harpreet
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86718 Visits: 41103
harpreets.singh (2/15/2012)
I tired to use powershell to get drive space info.

Did it work? :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63057 Visits: 19113
Nice script, but I'd argue this isn't using T-SQL. It's using a shell command, which isn't allowed in many places.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
harpreets.singh
harpreets.singh
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 76
Hi Jeff,

Try it and let me know if it works.

I have tested this script on several servers.

regards,
Harpreet
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86718 Visits: 41103
harpreets.singh (2/15/2012)
Hi Jeff,

Try it and let me know if it works.

I have tested this script on several servers.

regards,
Harpreet



BWAAA-HAAA!!!! Not the way it works. Since you're the one that recommended it, you first tell me that you have tested it and that it works. Then I give it a try and verify.

The only reason why I'm asking this is because your original comment was that you tried to do something. You didn't say if the script you pointed to actually solved your problem and I don't actually have the time tor read every article that someone recommends on an offhanded basis. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLDuck
SQLDuck
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 343
Thanks for sharing this, Sanjay. This script solves a problem that's been bothering me for a while!
prasenjit221
prasenjit221
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
Thank you Raymond..Your code helped me build mine.Hope this helps:
SET NOCOUNT ON
IF OBJECT_ID(N'tempdb..#drive_space') IS NOT NULL DROP TABLE #drive_space
CREATE TABLE #drive_space
(name varchar(5)
, available_mb int)
INSERT #drive_space(name,available_mb)
EXEC master..xp_fixeddrives
DECLARE
@drive VARCHAR(5)
, @cmd VARCHAR(1000)
, @pos SMALLINT
IF OBJECT_ID(N'tempdb..#cmd_space') IS NOT NULL DROP TABLE #cmd_space
CREATE TABLE #cmd_space
(total_b VARCHAR(1000)
, drive VARCHAR(5))
DECLARE drive_name CURSOR FOR
SELECT name FROM #drive_space
OPEN drive_name
FETCH NEXT FROM drive_name INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd='master..xp_cmdshell ' + ''''+ 'fsutil volume diskfree ' + @drive + ':| find '+ '"Total # of bytes"'+''''
INSERT #cmd_space(total_b) EXEC (@cmd)
UPDATE #cmd_space SET drive=@drive WHERE drive IS NULL
FETCH NEXT FROM drive_name INTO @drive
END
CLOSE drive_name
DEALLOCATE drive_name
DELETE FROM #cmd_space WHERE total_b IS NULL
SELECT @pos=charindex(':',total_b) FROM #cmd_space

SELECT b.drive as DRIVE
, CONVERT(BIGINT,(RIGHT(b.total_b,(LEN(b.total_b)-@pos))))/1073741824 AS TOTAL_DRIVE_SPACE_GB
, a.available_mb/1024 AS AVAILABLE_SPACE_GB
FROM #drive_space a WITH (NOLOCK)
INNER JOIN #cmd_space b WITH (NOLOCK)
ON a.name=b.drive
DROP TABLE #cmd_space
DROP TABLE #drive_space
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search