SQL Server – How to Reduce MDF File Size with 50 GB Reserved Space

Once the log claims disk space the only way to get it back is to shrink. Checkpoints and truncation will free up space within the log file (now available for future use by the log) but the actual footprint of the file on disk stays the same.

Read these articles for good information not only about how this works but the pro's and con's of messing with it. Most DBA's would agree that shrinking is not a good idea in most normal cases (as you eluded to in your question).

http://msdn.microsoft.com/en-us/library/ms365418.aspx

pros and cons

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/a9e92f1b-7a27-4999-b241-eadadbb0a057

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION 

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.