This is well known issue for everyone around ! – there are lots of article out there describe it , but its something “ODD MAN OUT” situation. even my own article which helped me last time didn’t help me in this case. When I tried the way I had in my article – I wasn’t able to restore my database with Error “Database Size is larger than permitted in express edition”

I tried to shrink my log file but it ends up with – “Cannot shrink log file … because all logical log files are in use”

After checking Transaction we found that it is waiting for one of the uncommitted transaction due to which the log files are in use all the time.

Using following steps I was able to shrink the log filel to 1024kb ( 1 MB) [ Database Recovery model was simple ]

SELECT name, log_reuse_wait_desc FROM sys.databases

This gives me clue about something wasn’t right with my database – when it says NOTHING for your database it make sure – your database is ready to shrink , if its not NOTHING , means replication ( or may be other ) task are going on.

EXEC sp_removedbreplication YOUR_DB_NAME

This will remove replication from database and now if you run first query – it should return NOTHING for your database. – that indicates all good now. let’s move on.

ALTER DATABASE YOUR_DB_NAME SET RECOVERY SIMPLE

It’s purely optional step to set your database recovery model to SIMPLE.

Now final step to shrink

DBCC SHRINKFILE (N’Your Logical Log Name’, 0, TRUNCATEONLY)

And that’s all – my log was 1024kb ( 1MB ) – from around 18 GB .

Happy Coding !