You'll surprise that your 200MB database take 10GB or event more for transaction log file (.LDF). In a on-going project I'm joined, the Log file of a development database take almost 15GB and I don't have that large free disk space to run that database. Then I tried to shrink with MS SQL Management Studio, but not enough, just take size down for 5%. Then need to get rid of the log file. Here is how to do it with MS SQL Management Studio
- Detach the database from the SQL Server Management Studio.
- Delete the log file (.LDF) of the database
- Attach the database without the log file. When you do this step make sure you highlight the log file entry in the wizard and remove it. And new log file (much smaller the old one) file created by the SQL Server.
That all. But you can do that only in development environment, Log file have it's important role and you might read more about it at
UPDATED: Pinal Dave have describe a more convenient method to do that at http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
USE DatabaseName GO DBCC SHRINKFILE(<TransactionLogName>, 1) BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY DBCC SHRINKFILE(<TransactionLogName>, 1)