Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Finding out Total Disk Space in TSQL Expand / Collapse
Author
Message
Posted Wednesday, June 17, 2009 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 10:11 PM
Points: 9, Visits: 168
Nice Query Raymond. Thanks
Post #736722
Posted Wednesday, June 17, 2009 1:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 10:11 PM
Points: 9, Visits: 168
Nice Query Raymond. Thanks
Post #736908
Posted Tuesday, September 13, 2011 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 29, 2012 6:55 AM
Points: 1, Visits: 8
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.
Post #1174466
Posted Wednesday, February 15, 2012 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 6:08 AM
Points: 3, 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
Post #1252459
Posted Wednesday, February 15, 2012 12:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,955, Visits: 30,245
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1252681
Posted Wednesday, February 15, 2012 12:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:15 AM
Points: 32,777, Visits: 14,938
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
Post #1252691
Posted Wednesday, February 15, 2012 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 6:08 AM
Points: 3, Visits: 76
Hi Jeff,

Try it and let me know if it works.

I have tested this script on several servers.

regards,
Harpreet
Post #1252705
Posted Wednesday, February 15, 2012 3:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,955, Visits: 30,245
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1252801
Posted Monday, July 09, 2012 10:10 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:52 PM
Points: 23, Visits: 271
Thanks for sharing this, Sanjay. This script solves a problem that's been bothering me for a while!
Post #1326957
Posted Sunday, October 14, 2012 8:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 10:19 AM
Points: 1, 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
Post #1372554
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse