Our database at works has only 10GB of space which is really not enough. We have tables with millions of rows and sometimes we need to either change the data or move it around. This usually enlarges the database space in the file system and we often get an error about insufficient disk space in the middle of running a query.
For the first two times it happened I called a friend of mine who is also a DB administrator. The second time it happened though he had me open a word document and create a how-to for compressing the database files. This is it.
1. Connect to the database computer with msdtc
2. Open SQL Server Management and connect with your SA account
3. Right click the database –> Properties –> Options: If the Recovery Mode is not Simple change it to Simple (you will have to change it back in the end):
4. Right click the database –> Tasks –> Shrink –>Files:
File Type = Log
Shrink action = Release unused space
In Available free space you can see how much you are going to free and you can chose if for example 4.28MB is really worth waiting for the shrink to end.
5. Right click the database –> Tasks –> Shrink –>Database:
![sql-server-2008-shrink-database[4]](http://lh4.ggpht.com/-bV8vlFIz8Gk/TfC8Ndc6WJI/AAAAAAAABzs/fzRvUdZfkew/s1600-h/sql-server-2008-shrink-database%25255B4%25255D%25255B5%25255D.png "sql-server-2008-shrink-database[4]")
Again in Available free space you can see how much you are going to free and you can chose if for example 1203.96MB is really worth waiting for the shrink to end (for me it is….).
Be warned that this could take some time.
7. If in step 3 you changed the Recovery Mode to Simple that you will have to change it back to your original value. If it was Simple you can simply skip this step.
That it – enjoy the free space…
keywords: SQL Server 2008, Free space, Hard Disk, recovery mode, database, DBA
