When I am not blogging, I am typically working on SQL Server Optimization projects. Time and again, I only have access to SQL Server Management Studio that I can remotely connect to server but do not have access to Operating System, and it works just fine.
At one point in optimization project, I have to decide on index filegroup placement as well TempDB files (.ldf and .mdf) placement. It is commonly known that system gives enhanced performance when index and tempdb are on separate drives than where the main database is placed.
As I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. Based on this result, I decide my strategy of where to put my indexes and tempdb.
EXEC master..xp_fixeddrives
Reference : Pinal Dave (http://blog.sqlauthority.com)
This is a nice handy way. But be aware that if any network drives are mapped on server then those will not be shown through this procedure.
Hi Pinal,
i’m in love all the articles you write.
I’ve one question regarding View:
its just a plain view:
Select ID, ’1′ as group, Name From T_Base;
Output
———
ID Group Co_Name
1 1 ABC
2 1 PQR
1 1 ABC
3 1 MNO
1 1 ABC
Now, i would like to update / increment the group value by 1 if the ID value gets repeated. I wanted to do everything in that particualr VIEW only.
Expected Output:
———————–
ID Group Co_Name
1 1 ABC
2 1 PQR
1 2 ABC
3 1 MNO
1 3 ABC
Any help would be appreciated.
Thanks in Advance,
Raj
Which version of SQL Server are you using?
If you use version 2005, use row_number() function
Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
SELECT ID,ROW_NUMBER() OVER (PARTITION BY ID,Co_Name ) AS Group, Co_Name
FROM [your_table]
Network drives shouldn’t be used to host SQL Server databases, so that shouldn’t be a problem.
@Pinal Dave
Thank You once again for this nice post. The post is really nice….
@Atif.
Is there any way we can check Hard Drive Free space in network drives if it’s there?
Thanks in advance.
and again, Pinal neverending story ;) with (not only) tips is going on ;)
Thx, fot that! This one can be handy for some scripting..
Hi Sir,
Nice Knowledge Sharing.
Raj,
Is this what you are looking for?
DECLARE @t TABLE (ID INT, Name CHAR(3))
INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
INSERT INTO @t (ID, Name) SELECT 2, ‘PQR’
INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
INSERT INTO @t (ID, Name) SELECT 3, ‘MNO’
INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
SELECT
ID,
ROW_NUMBER() OVER(
PARTITION BY ID ORDER BY ID) AS [Group],
Name
FROM @t
/*
ID Group Name
———– ——————– —-
1 1 ABC
1 2 ABC
1 3 ABC
2 1 PQR
3 1 MNO
*/
Mount Points don’t show up with this… standard practice used for VLDB’s… So, this shows that I have an E drive for example that is a couple hundred MB…but, I have 3 TB worth of mountpoints hanging off that E drive.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=301832
Thank you very much Jacob for your reply. It resolved my problem.
Now i have some different requirement. Could you please help me out with it as well /
This is the query rewult which im getting as of now
======================
Code Name Group
——————————–
AX001 ABC 1
AX002 XXX 1
AX001 MNO 1
AX001 ABC 1
AX003 YYY 1
AX001 PQR 1
AX004 ZZZ 1
AX003 YYY 1
AX003 ZZZ 1
AX002 ABC 1
I need the result in the below format.. When Code & Name are same then it shud start with 1, if same code & diff Name then the Gropu value should increment by 1.
Expected Output
======================
Code Name Group
——————————–
AX001 ABC 1
AX001 ABC 1
AX001 MNO 2
AX001 PQR 3
AX002 ABC 1
AX002 XXX 2
AX003 YYY 1
AX003 YYY 1
AX003 ZZZ 2
AX004 ZZZ 1
Can you please reply to me on my id. I sent you the Linkedin request as well.
Thanks,
Awaiting your feedback / reply ASAP (TIA)
Raj,
You can use the DENSE_RANK() function to achieve this. I am posting it here (even though you asked me to send by email) so that it can help other people as well.
DECLARE @t TABLE (Code CHAR(5), Name CHAR(3))
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’ABC’
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’ABC’
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’MNO’
INSERT INTO @t (Code, Name) SELECT ‘AX001′,’PQR’
INSERT INTO @t (Code, Name) SELECT ‘AX002′,’ABC’
INSERT INTO @t (Code, Name) SELECT ‘AX002′,’XXX’
INSERT INTO @t (Code, Name) SELECT ‘AX003′,’YYY’
INSERT INTO @t (Code, Name) SELECT ‘AX003′,’YYY’
INSERT INTO @t (Code, Name) SELECT ‘AX003′,’ZZZ’
INSERT INTO @t (Code, Name) SELECT ‘AX004′,’ZZZ’
SELECT
Code, Name,
DENSE_RANK() OVER(PARTITION BY Code ORDER BY Name) AS[ Group]
FROM @t
/*
Code Name Group
—– —- ——————–
AX001 ABC 1
AX001 ABC 1
AX001 MNO 2
AX001 PQR 3
AX002 ABC 1
AX002 XXX 2
AX003 YYY 1
AX003 YYY 1
AX003 ZZZ 2
AX004 ZZZ 1
*/
Dear Pinal & Other members,
I want to find out full hard disk detail with space utilized (In MB) and percentage, space utilized in MB and % for a system.
it’s really urgent so please co-operate.
Thanks in Advance.
Regards,
Bipin Singh
Try
EXEC xp_cmdshell ‘Dir “Driver”‘
Dear Madhivanan,
It returns something like this,
Volume in drive C has no label.
Volume Serial Number is 1CB7-C6C4
NULL
Directory of C:\WINDOWS\system32
NULL
File Not Found
NULL
which is not my desired output. I discovered something like this which is satisfying my requirement.
Script:
Alter PROCEDURE Proc_diskspace
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
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
UPDATE #drives SET TotalSize=@TotalSize/@MB, ServerName = @@servername, 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, TotalSize as ‘Total(MB)’, 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
Exec Proc_diskspace
——————————————————————–
It results like this,
Servername C 38154 26468 69 2010-05-05 09:43:02.300
Thanks,
Bipin Singh
Hi Pinal,
Wat about mounted volume?
Thanks
Good to know the T-SQL way of doing it but this is where PowerShell comes into picture. It is quite easy!
Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize
This gives an output similar to:
DeviceID FreeSpaceMB
——– ———–
C: 258795.73046875
D: 0
F: 3000.8125
H: 122916.5
Q: 0
Now, you can easily extend this to query a remote server by just adding the -ComputerName parameter. For example:
Get-WmiObject -ComputerName Remoteserver -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize
Simple!
[...] a List of Fixed Hard Drive and Free Space on Server. I recently received excellent comment by MVP Ravikanth. He demonstrated that how the same can be done using Powershell. It is very sweet and quick [...]
Hi Pinal and All commentors,
The values in sql output are different from the OS drives actual values.
eg:D drive is showing 19313 MB in sql result.But OS drives shows 18.8 GB free..
How can we rectify the difference?
@Raj
19313/1024 = 18.8GB
Thanks Raj..
how to get the free space in GB