I have a development db that's running on sql 2014 in 2012 compatibility that I'm having an issue with trying to shrink the log file. The mdf is 60+gb and the log is 80+gb but since it's dev like to shrink this down. I've tried shrinking the log after backing up the db from both the gui and command line but doesn't work. I switched the db to simple recovery model, backing up and trying to shrink multiple times but still nothing. I've also tried dbcc shrinkfile to either truncate the file or set it to a specific size but nothing.. when I run dbcc loginfo I do see entries with the status '2' but backing up the db/log in various attempts doesn't seem clear this out like normal. Since its dev I even tried deattaching and reattaching without the log and it throws an error, if I try to create the db (that's still unattached) using 'for attach' or 'for attach_rebuild_log' it will either fail or create the log file with the same size and issues as before. I checked the log_reuse_wiat_desc and it shows 'XTP_CHECKPOINT' which I believe is an in memory setting so wondering if the developer was testing any in memory objects if it could have caused this issue?

Sure appreciate any thoughts on this! again since it's a dev db, that's backed up, its not a show stopping issue but like to figure this out in case it happens in prod.

Thanks, Jeff


Is the database in FULL or SIMPLE recovery mode?

One thing that you could try doing is putting the database into SINGLE_USER mode and then shrinking the log file.

I have had success with troublesome systems before by taking two log backups in quick succession and then shrinking the file. Put all the commands in one script window and execute them to get the commands to execute in rapid succession.

If the database is in FULL mode, then make sure you are taking regular LOG backups; this will keep the file smallish.

| improve this answer | |

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