All we need is an easy explanation of the problem, so here it is.
I installed SQL Server + SSMS for my univ class and ever since then my disk space is going missing (every so often, approx. 3 gb of space goes missing). I strongly suspect SQL Server and/or SSMS to be at fault (since it only happened after I installed them), however I have no idea how to track down that missing space. Is it doing massive amounts of logging? Backups? No idea.
Where might I find that disk space and/or check if it is SQL Server’s and/or SSMS’ shenanigans?
Thank you for your time.
Note: I am using SSMS 18.9.1 and SQL Server 2019 Developer Edition. I might be able to update but any problems that arise will be my fault for deviating from the officially provided versions.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
SQL Server doesn’t take backups on its own, so it’s unlikely those are the culprit unless you set them up via a maintenance plan or other script.
More likely is that:
- Your database is in full recovery and you’re not taking log backups, in which case you can switch it to simple recovery model so that SQL Server manages the transaction log a bit for you
- Your data/log files are set to grow by a percent, in which case you can set them to grow by smaller more predictable increments so they don’t grow by exponentially larger percentage-based increments
If the goal is to reclaim disk space, feel free to search for any number of scripts or GUI walkthroughs out there to accomplish that task.
It’s a bit more work to set up, but even for laptop instances of SQL Server, I always make sure to create a new drive and set that as my default database file location. Since you’ve already installed SQL Server, you’d want to follow guidance for changing the default database location.
If you’ve got databases already deployed that you cannot lose, then you may want to move the database files to a new drive once you have it set up.
Note this will not stop SQL Server from filling the drive, but it will contain the damage to that drive and protect you from a full C:\ drive.
As an aside, this actually saved me a pretty big headache on a laptop lab very recently where I misconfigured the Polybase setup and filled up the dedicated SQL Server drive in a matter of days.
As you can see in the documentation SQL Server 2019: Hardware and software requirements SQL server is using some space on disk. The exact space depends on the option & features you installed but the space you are missing is in line with the requirements.
Take a look at the folders "C:\Program Files\Microsoft SQL Server" and "C:\Program Files (x86)\Microsoft SQL Server" for 32 bit components like SSMS.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂