I have a datawarehouse with database log of 800 GB.now I want to shrink it or reduce the disk space occupied by Log.ldf file. I tried to shrink file in several ways - took full backup , performned transaction log back up, modified recovery mode from Full to Simple, executed dbcc command however none of them seems to help in reducing log size at disk.

I also detached database, deleted log file but due to memory-optimize file container I got error while I was trying to attach it again (I had read SQL server will automatically add a log file but seems it doesn't work when database has a memory_optimize file).

After all the above attempts database log file is still 800 GB and I am not really sure how to clean up space consumed by database log. Appreciate if I could get some expert opinion on this issue or guide me in case I am missing something.

  • What is the result of the following query? SELECT log_reuse_wait_desc FROM [master].[sys].[databases] WHERE [name] = 'yourdb'; – NedOtter Aug 12 '18 at 13:54

I completely agree with Biju, I had similar case when log size was not reducing at all however this was in Sybase, nevertheless concept is same, you may refer the case here:

Database Backup size not reducing

You can also use sp_WhoIsActive to check what is currently running at your database and what is the underlying command, who is running it, from how long this is running and many other details; this would help you in finding whether you should kill the specified spid or should consult with user before taking any action. I hope this should help.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.