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

agassan
  • 13
  • 2

2 Answers2

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.

Evan Anderson
  • 141,071
  • 19
  • 191
  • 328
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.

mike42
  • 86
  • 7
  • 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 http://www.codeproject.com/KB/database/truncate_log_SQL_server.aspx – agassan Sep 11 '10 at 17:42