Your first option for getting some extra disk space for SQL Server is to simply use NTFS compression. You can only do this with secondary files and they have to be read only files. This is definitely going to limit the circumstances in which you can use it; but if it does work for you, it is pretty easy to enable.
Pick the Properties of that file, hit Advanced, and select Compress contents to save disk space. Now, this is going to incur a little bit of a performance hit. You see, SQL Server is going to request the data from Windows. Windows is going to read the compressed data from disk, decompress it in memory and hand the decompressed information off to SQL Server. You certainly want to test this before you commit to it, but if you do have databases that have historical information in them or something like that, and you can move that information to a secondary file, then that becomes an option.
Of course, you get a little bit more flexibility in SQL Server itself, because the Enterprise edition supports Database Compression built right into the product.
To do this, we are going to select Storage, Manage Compression, and we will get into the Data Compression Wizard. In this case, I am going to start by selecting Row level compression, which is the lesser of the two compression techniques. But, lesser does not always mean it is going to be less effective. It does create less of a performance hit, and it works mainly by kind of modifying the actual data type used to store your data. I will hit Calculate here and you can see that my current table takes up 761 MB and with Row Level compression I can get it down to 11. That is a massive, massive benefit for 97 thousand rows.
Selecting Page level compression shows that I really would not save that much more, although Page Level Compression does have a higher impact on your CPU. Compression is defiantly not something you want to turn on a day to day server that is already experiencing some CPU related performance issues. If you have a little bit of extra CPU that you can dedicate to compression, than it is a good way to save some disk space. Page level compression does save you more space but depending on the actual form of your data on disk it might not save you a lot more space than the less performance intensive Row level compression. It can take a while to apply this type of compression, you can certainly schedule that to run later, do it during an off hours maintenance window or something like that.
Now, another thing that can take up a lot of space with SQL Server databases, are of course, those wonderful backup files and you do have some ability to do Compression right with in SQL Server itself. Select the Database, tell it to run a Backup and you do have some Compression options, over on the Options tab. You can certainly save a lot of disk space that way and, honestly, if you’ve just got maybe a tape drive hooked up to SQL Server and you back right up to that, then that is a pretty good option.
I think you will find though, if you hop on a search engine, punch in “SQL Back Up Compression”, you will find that the third party independent software vender space has a lot more ideas for improving your disk space utilization when it comes to your SQL Backups, sometimes by as much as 90% using a combination of pretty powerful Compression algorithms, but also other techniques like de-duplication. In fact, if you find yourself using disk to disk type backups or any of a number of more-modern styles of backup to SQL Server you can really save a ton of space.