Reduce space used on C drive

Question

I have installed SQL Server 2008 R2 on my laptop, under 32-bit Windows 7.

The SQL Server installation is using approximately 30GB of space on my C: drive. Why is it using so much space, and how can I reduce it?

Answer 1

This is not the installation of SQL Server itself. It is much more likely one of the following:

• a log file for a database (or databases) with a lot of activity, in full recovery mode, but you have never taken a backup or a log backup.
• a data file that you have populated with a lot of data (perhaps inadvertently, like tempdb) or attached a large database from somewhere. This could also be filestream data; let us know if you have used that feature.
• a large backup or a long series of backups, if in fact you are taking backups.

First, identify which file(s) are actually large - they're almost certainly .mdf / .ldf, but also check for .bak and .trn. Start in:

C:\Program Files\Microsoft SQL Server\<instance>\MSSQL\DATA\
C:\Program Files\Microsoft SQL Server\<instance>\MSSQL\Backup\

(But you may have to also check other locations you might have told SQL Server to use for data/log files, backups, filestream, etc.)

Then:

• If the large files are .mdf / .ndf files, and they are user databases, you need to decide whether you want to keep those databases around, or keep the large tables within them. For databases you know you don't need, you can just drop them; for databases you might need, you can take a full backup and set them offline temporarily until you are certain you don't need them, then drop them. You can also just drop individually large tables inside an existing database, then shrink the data file to reclaim space (please read this entire post and also this one for more details); you can see which tables are largest using DMVs like sys.dm_db_partition_stats.

  If they are system databases (like tempdb), then your next course of action depends on which system databases they are, why they are large, and whether you have alternative places to move the ones that are simple to move.

• If the large files are .ldf files, and they are for databases you want to keep, you can clean up and reclaim space using a series of operations depending on your recovery model. Please read this post and all of its answers, in full, for a lot more detail.

• if the large files are .bak / .trn, then you need to decide how far back you want to keep your backups, and which (if any) are ones you need to keep. Shift+Delete the ones you don't want (to bypass the Recycle Bin; at least I think that's how I remember to do it on Windows).

Or:

• Move SQL Server (or the large databases) to a bigger drive (add a drive to your system, use an external drive with more capacity, etc). This may be easiest to accomplish by just installing a new instance and putting as many of the things away from C:\ as you can.

Answer 2

Even if you keep your databases on other drives the default installation puts the system databases on C:. This includes tempdb which can grow fairly large depending on your application(s). This can be moved though, see here amongst other places.

Another possibility for SQL server suddenly seeming to take more space on C: is that backups go there to by default within a standard install so someone could have dropped some backups there instead of where you normally send them. Of someone has left a trace logging to disk that too could be the culprit. For a useful view of what is using your space, try running something like this over you drive for a useful but of what is using your space (run it elevated or there are some things it might miss) to confirm that it is one of these things (or a mix of them).