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 (https://blog.sqlauthority.com)
39 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
HI Pinal
How do you move databases to a bigger drive SQL2012 High Availabilty
Hi Pinal,
How do create sp for disk space information(In Gb).Means it will send diskspace information through sp to mail
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
Hi Pinal,
Please help me in how to automate space check using scripts..
How to check hard disk type on my SQL server using SQL scripts? Thank you!
How about MySQL?
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?
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
Want to capture available diskspace of another server which is an application server where sql is not installed.
Is there any way ??
Is there a way ti get free space in percent?