Sql-server – Is it wise to drop fixed-length columns to free up space

Removing data will not release the space used by to the filesystem unless you then perform a database (or per file) shrink. The newly unallocated space will get used by new data as it comes in before any more is claimed from the OS by growing the relevant files.

If you need to fully reclaim the space then you need to look at DBCC SHRINKDATABASE and DBCC SHRINKFILE, though don't do this unless you have to as it can cause internal fragmentation that if allowed to build up can impact performance.

When you take a backup only used pages are included, so you will see backup sizes drop even though the actual database size has not reduced.

If you've dropped all the tables, you could just drop the database and re-create it. It will be created with the default sizes and growth rates as defined by the model database. These may not be ideal (I don't find the defaults particularly useful in most scenarios), but you can change them.

Unless you have a lot of things set up on this database (like Service Broker, a ton of users, etc.), I think that is less cumbersome than getting into the habit of growing/shrinking growing/shrinking growing/shrinking. You should put the database on a drive that has enough space to accommodate the data you're going to generate, without having to shrink all the time. Shrinking and growing are both expensive events, especially on slow I/O.

Also, to prevent the log from growing to 9 GB (that seems pretty ridiculous for the data you describe), you should either be full recovery and you need to perform more frequent backups and log backups, or you should switch to simple recovery. More details here - it's long but please read it all.