So you hear this spread across the Twitterverse and Blogosphere. You should not RDP your SQL Servers to do administrative work. My nature has always been to troubleshoot issues from the server in an RDP session.
When I received a disk space alert on a development system I was about to RDP and do my thing. But I said wait, let me approach this from a different perspective…
To the rescue is dbatools as always seems to be the case these days.
So I ran the following command to see which drive needed attention. Even though I already knew from the alert that fired, I just wanted to see if I could build an entire workflow for this type of situation.
Get-DbaDiskSpace -ComputerName ServerName
Now I see that the R: drive is my culprit and only has 2.64% free space remaining. So lets dig in to that drive. I know that is our standard data drive and has all the SQL MDF and instance files.
Next up I ran the below command to see what if any SQL Server files could be shrunk to recover some space on disk to avoid having to add space to the drive or purge data from a database.
Get-DbaDbSpace -SqlInstance ServerName| ogv
From this screen shot we have a 26GB file with 11GB of free space available.
Now lets shrink that file and reclaim some disk space. So running this command will shrink the data file to reclaim as much free space as possible in the data file.
Invoke-DbaDbShrink -SqlInstance ServerName-Database TSSPReference -FileType Data
That gains us 11 GB. However, on a 2TB drive that gives us very few percentage points back. So it is time to keep looking. We see that file will get us 1GB back, but still will not get our alert cleared which is at 5% free space.
So now on to purging options. Why do we have a 1.5 TB staging database???
That seems like a good method for fixing and troubleshooting a disk space issue without RDPing the server. So maybe it is possible, especially
with our friend dbatools.