Home / server / Questions / 230613
Accepted
Robin
Asked: 2011-02-04 05:22:05 +0800 CST

SQL Server 2005 log file not truncating

  • 772

Got something strange (to me ) going on with my SQL Server 2005 database. The log file has very gradually grown huge.

Running 

dbcc loginfo('dbname')

shows everything in the log is active (Status 2)

Running

dbcc opentran

shows 'no active open transactions'

so.... Not really sure what all the stuf in the transaction log is. The only glimmer of hope I've found is this post at sql server central but turning replication on and off seems a bit of a peculiar fix (sort of a variant of turn it off and on again I guess).

Any ideas what's up or what else to try/investigate?

thanks, Robin

sql-server-2005 transaction-log

2 Answers

  1. Best Answer

    The transaction log doesn't manage itself. You either need to schedule a regularly ocurring log backup and truncate or you need to set the database Recovery model to Simple.

    My suggestion would be to perform a log backup and truncate to get the log file down to a more reasonable size and then set the database Recovery model to Simple to avoid this problem in the future.

    • 1

  2. you can check sys.databases for finding out what is holding the log backup from getting shrink and go from there use select name, log_reuse_wait_desc from sys.databases

    • 0