Question

Here's the error message:

10/17/2009 1:00 AM (GMT-04:00): The Fog Creek Maintenance Service has reported an error. (200) CCmd: The transaction log for database 'fogbugz' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
SQL:  DELETE FROM FBLock  WHERE ( sLock LIKE 'SessionWriteLock[_]%' ) AND ( sKey = '' OR sKey IS NULL OR dtExpires  System.Data.SqlClient.SqlException: The transaction log for database 'fogbugz' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
  at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
  at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
  at Wasabi.Runtime.ADO.Command.ExecuteNonQuery() in c:\src\Wasabi\Wasabi.Runtime\ADO\Command.cs:line 94
  at FogCreek.FogBugz.CCmd.ExecuteNonQuery(Nullable`1 nCacheClearLevel) in c:\src\FogBugz\distribution\src-Website\CCmd.was:line 236
  at Wasabi.Runtime.Error.Raise(Int32 number, String source, String description, String helpfile, Nullable`1 helpcontext) in c:\src\Wasabi\Wasabi.Runtime\Error.cs:line 88
  at FogCreek.FogBugz.CCmd.ExecuteNonQuery(Nullable`1 nCacheClearLevel) in c:\src\FogBugz\distribution\src-Website\CCmd.was:line 245
  at FogCreek.FogBugz.Database.CDeleteQuery.Execute() in c:\src\FogBugz\distribution\src-Website\CDeleteQuery.was:line 25
  at FogCreek.FogBugz.CDbSession.DeleteExpiredLocks() in c:\src\FogBugz\distribution\src-Website\CDbSession.was:line 334
  at FogCreek.FogBugz.CDbSession.DeleteExpired() in c:\src\FogBugz\distribution\src-Website\CDbSession.was:line 298
  at FogCreek.FogBugz.CDailyTask.PerformTask(String& sError, String sURL) in c:\src\FogBugz\distribution\src-Website\CDailyTask.was:line 65
  at FogCreek.FogBugz.__Global.SingleHeartbeat(Int32 cMaxBayesianRequests, Int32 cMaxMailMessages, Int32 nEmailTimeout) in c:\src\FogBugz\distribution\src-Website\singleHeartbeat.was:line 66
  at FogCreek.FogBugz.__Global.StartHeartbeat() in c:\src\FogBugz\distribution\src-Website\heartbeat.was:line 69

We tried truncating the transaction log, but it immediately fills right back up.

Answer 1

I spoke with David Fullerton on the phone a few days ago and he used Copilot to poke around on our server and fix the problem. It turns out the FBLock table had grown out of control. It had nearly 200,000 rows. He stopped the FogBugz Maintenance service, emptied it, restarted the service, and everything was back to normal. I kept an eye on it for a day and this table only had about 15 entries, so you could say 200K was an excessive number of entries. David told me that part of FB's daily maintenance routine is to empty this table. For reasons unknown, FogBugz's attempt to delete the contents of this table was maxing out the transaction log, thus causing the operation to fail.

So, how did this table get so huge? That's still a mystery, but it does have something to do with the infernal Perforce Defect Tracking Gateway (P4DTG). That thing polls Perforce and FogBugz every 5 seconds to see if there is anything that needs to be done. On every 5 second cycle, three new rows are written to the FBLock table in the FogBugz database. FogBugz normally clears this table each day, but last Friday it couldn't because it had somehow gotten too large.

I had turned the P4DTG off before I talked to David, and left it off until the following morning. That day the FBLock table was stable at around 15 entries. I turned on the P4DTG and the table started growing, however FogBugz appears to be successfully emptying it each day. If it weren't it would probably have more than 100K entries by now, but it only has about 20K.

Answer 2

Have you tried changing the transaction log from Full to Simple?

http://stackoverflow.com/questions/996403/disable-transaction-log

Answer 3

Let's start with the obvious. Have you backed up your DB in a while? If not, try backing it up THEN truncating the log.

Truncating won't remove any log entries that have been generated since the last backup because it needs them to recover in case of a DB failure.