SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server

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

SQL SERVER - Get a List of Fixed Hard Drive and Free Space on Server hddrive

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Forgot the Password of Username SA
Next Post
SQL SERVER – Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime

Related Posts

38 Comments. Leave new

  • Hi PInal

    I have scenario relating to Tempdb .. in my server(test) i have 50 gb drive and 15 gb is free when i am running my SISS package to extract data from different source my tempdb size is reaching to 14 gb but the moment i stop job and re-start sql server services tempdb comes to 8 mb ..

    I have another server(dev) where c drive is 40 gb and 14 gb is free but there is d drive 700 gb but i checked tempdb files are still in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

    Now the SISS package take 4 hr to get completed in dev but its taking 3 days in test can tempdb size increasing is reason for it .. if so how we can fix it

    Reply
  • HI Pinal

    How do you move databases to a bigger drive SQL2012 High Availabilty

    Reply
  • Hi Pinal,

    How do create sp for disk space information(In Gb).Means it will send diskspace information through sp to mail

    Reply
  • I want to automate scripts for space check to be done daily basis.. Could anyone pls guide me how to create scripts for automating in SSMS 2012

    Reply
  • Hi Pinal,

    Please help me in how to automate space check using scripts..

    Reply
  • How to check hard disk type on my SQL server using SQL scripts? Thank you!

    Reply
  • How about MySQL?

    Reply
  • Hi Pinal, I’m using a script that queries master_files and cross apply to the dynamic mgt function sys.dm_os_volume_stats I’ve no problem with this query if my collation is Latin1_General_CI_AS. However, it won’t give me the mount point details if the collation is SQL_Latin1_General_CP1_CI_AS. Have you come across this so far?

    Reply
  • The initial stored procedure returns zero rows for me. This suggests a method that could be available:

    The second method requires VIEW SERVER STATE permission per this article
    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-volume-stats-transact-sql?view=sql-server-2017

    Reply
  • carsten hoffmen
    August 1, 2016 3:43 pm

    Want to capture available diskspace of another server which is an application server where sql is not installed.
    Is there any way ??

    Reply

Leave a Reply

Menu