1

I tried to shink database log file but it is still huge. My database is 200mb and log is 30Gb USE [databasename]; BACKUP LOG [databasename] WITH TRUNCATE_ONLY; DBCC SHRINKFILE ('databasename_log', 1, TRUNCATEONLY);

Does not help much. Even after full backup

2 Answers 2

1

You may have open transactions in the log. Do a DBCC OPENTRAN on the log and see if you do. An open transaction is going to prevent you from shrinking the log.

There's a nice blog entry here that can give you more details about what's going on behind the scenes and gives you some background on the undocumented but handy DBCC LOGINFO command.

0

Maybe you have an open transaction that has not been fully committed. In SSMS use the activity monitor and look for connections with an open transaction (there should be a column called open transaction count). You may need to disregard your transaction that is querying the activity. If there is an open connection investigate what it is or kill it.

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .