Commit fail and transaction log is full

Hi,

Our testing server experienced a timeout exception when execute System.Data.SqlClient.SqlTransaction.Commit() in SQL Server 2000 thru .Net Framework 1.1. And this happened at 2007-02-13 18:07:05,954.
It was strange to us that all the insert statement can be executed without error within the transaction but the commit operation fails.
Moreover, after about 40 minutes, we found that the transaction log of this database is full.

Here is the exception and SQL Server 2000 Error Log:
Exception Stack Trace:
DateTime: 2007-02-13 18:07:05,954
Req Id: bccdae08-cc47-4f85-8f48-5f0b9dbbbf88
Exception: MyDatabaseException
Detail:
MyDatabaseException:
Index #0
Server: MySQLServer
Source: .Net SqlClient Data Provider
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
LineNumber: 0
Procedure: ConnectionRead (recv()).
State: 0
Error Number: -2 > System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlTransaction.Commit()

SQL Server Error Log:

2007-02-13 13:02:23.72 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944769, first LSN: 9434:22326:1, last LSN: 9434:22360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL\Backup\MyDatabaseName.bak'}).
2007-02-13 15:50:52.40 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944970, first LSN: 9442:38096:1, last LSN: 9443:748:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#4f96edfd-7fdb-4cd1-a740-3fe9a54d66c6'}).
2007-02-13 18:48:51.42 spid66 Error: 9002, Severity: 17, State: 6
2007-02-13 18:48:51.42 spid66 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:52:40.61 spid58 Error: 9002, Severity: 17, State: 6
2007-02-13 18:52:40.61 spid58 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:53:22.69 spid61 Error: 9002, Severity: 17, State: 6
2007-02-13 18:53:22.69 spid61 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:54:01.11 spid57 Error: 9002, Severity: 17, State: 6
2007-02-13 18:54:01.11 spid57 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..

Are these 2 things related? Would a full transaction log cause a commit operation fail?

Please kindly advice.

Thanks a lot.

the error is self descriptive and the transaction log drive in the machine have no space now. What u need to do now is

(a) Truncate the Log

backup log SomeDatabase with truncate_only

(b) Shrink the log file

dbcc shrinkfile(SomeDatabaseLogicalFileName_Log)

(c) Take FUll Backup -- Very important otherwise the backup chain will break if it is in full recovery

backup database SomeDatabase to Disk='D:\SomeFile.bak' with init

To control the Future growth read the article mentioned below

Refer

http://support.microsoft.com/kb/873235

http://www.databasejournal.com/features/mssql/article.php/1460151

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894

Madhu

commit /rollback problem

the exeception as followAn unhandled exception of type 'System.NullReferenceException' occurred in system.windows.forms.dll

Additional information: Object reference not set to an instance of an object.

my code as followDim meetError As Boolean = False
Dim conn As Odbc.OdbcConnection
Dim trans As Odbc.OdbcTransaction
Dim cmd As Odbc.OdbcCommand
Dim da As System.Data.ODBC.OdbcDataAdapter
Dim ds As DataSet = New DataSet("dsTable")
Dim dt As DataTable
Dim i As Integer
'
Try
conn = New Odbc.OdbcConnection(connStr)
conn.Open()
cmd = conn.CreateCommand
cmd.Transaction = trans
'
Catch e As Odbc.OdbcException
MsgBox("open error " & e.Message)
meetError = True
Return False
End Try
'
'exec sql
Try
For i = 0 To in_stmt.GetUpperBound(0)
cmd.CommandText = in_stmt(i).value
da = New Odbc.OdbcDataAdapter(cmd.CommandText, conn)
cmd.ExecuteNonQuery()
'
Try
dt = New DataTable(i.ToString)
da.Fill(dt)
If dt.Columns.Count > 0 Then
ds.Merge(dt)
End If
Catch ex As Exception
MsgBox("merge error " & ex.Message)
End Try
Next
Catch ex As Odbc.OdbcException
MsgBox("execute error " & ex.Message)
meetError = True
End Try
'
If meetError Then
trans.Rollback()
Else
trans.Commit()
End If
'
'close connectoin
'
grd.DataSource = ds
Try
conn.Close()
conn = Nothing
Catch e As Odbc.OdbcException
MsgBox("close error " & e.Message)
End Try
Return True
thanks for help.I think the problem is
can da.fill(ds) after trans.begin but before trans.commit.
thanks