As a SQL dba, we all manage disk spaces. Whether it is associated with data drive, log drive or even TempDB drive.
In one particular scenario, it can be bit tricky or rather I would say bit time consuming to get the correct insight on what is taking most spaces in your volume.
This scenario is where you are hosting several databases and their files on the same drive or mount points and each database files are managed in different file group and folders.
One way to know is by manually logging on to the SSMS and actually sees the size of each database files and location.
But the way, I am going to suggest you here is to use to our great PowerShell script. All you have to do is simply remote desktop and open/execute PowerShell script.
For example: to get all the files under c:\temp location and their space usage (in %), you can execute below script:
$path = "c:\temp"
$totalfilesize = [int]((Get-ChildItem $path | Measure-Object -Sum Length).Sum / 1kb)
Get-ChildItem $path | Select-Object Name, CreationTime, @{Name="kb";Expression={$_.Length / 1KB}}, `
@{Name="Percent";Expression={(($_.Length / 1KB)/$totalfilesize )*100}}| sort Percent -descending;
This is very handy especially when you are managing lot of low disk space alerts.
In one particular scenario, it can be bit tricky or rather I would say bit time consuming to get the correct insight on what is taking most spaces in your volume.
This scenario is where you are hosting several databases and their files on the same drive or mount points and each database files are managed in different file group and folders.
One way to know is by manually logging on to the SSMS and actually sees the size of each database files and location.
But the way, I am going to suggest you here is to use to our great PowerShell script. All you have to do is simply remote desktop and open/execute PowerShell script.
For example: to get all the files under c:\temp location and their space usage (in %), you can execute below script:
$path = "c:\temp"
$totalfilesize = [int]((Get-ChildItem $path | Measure-Object -Sum Length).Sum / 1kb)
Get-ChildItem $path | Select-Object Name, CreationTime, @{Name="kb";Expression={$_.Length / 1KB}}, `
@{Name="Percent";Expression={(($_.Length / 1KB)/$totalfilesize )*100}}| sort Percent -descending;
This is very handy especially when you are managing lot of low disk space alerts.
