Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
Found great tips about how to reset Database Log file by detaching database, deleting log file then attaching database without log file. This will cause database to create a new log file.file – agassan Sep 11 '10 at 17:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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