.net and transaction logs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Lately the size of our transaction logs have increased substantially and I
just need to know it's not something wrong with my knowledge of ado and
programming. I've never had formal tranning on ADO.

Is the following is the ideal way to execute an Update?
System.Data.SqlClient.SqlConnection sqlConnect = new
System.Data.SqlClient.SqlConnection(BOM.SqlServerConnect);

System.Data.SqlClient.SqlCommand sqlCommand = new
System.Data.SqlClient.SqlCommand(Query, sqlConnect);

sqlConnect.Open();
sqlCommand.ExecuteNonQuery();
sqlConnect.Close();

There is no commit here?
also, when writing stored procedures, should there be commits?

Does anyone know of website with best practices for ado and MS Sql?
 
Brett said:
Lately the size of our transaction logs have increased substantially and I
just need to know it's not something wrong with my knowledge of ado and
programming. I've never had formal tranning on ADO.

Is the following is the ideal way to execute an Update?
System.Data.SqlClient.SqlConnection sqlConnect = new
System.Data.SqlClient.SqlConnection(BOM.SqlServerConnect);

System.Data.SqlClient.SqlCommand sqlCommand = new
System.Data.SqlClient.SqlCommand(Query, sqlConnect);

sqlConnect.Open();
sqlCommand.ExecuteNonQuery();
sqlConnect.Close();

There is no commit here?
Click to expand...

By default SQL Server is in autocommit mode. This causes each statement to
be commited as soon as it's issued. This can be bad for performance, and if
you are issuing many updates in quick sucession, you should run them in an
explicit transaction.
also, when writing stored procedures, should there be commits?
Click to expand...

Stored procedures should, in general, not have commits. The client is in
charge of transaction scope, and if the client wants to run multiple
procedures in the same transaction, the stored procedure should allow that.

How often you commit will not have a large effect on the size of the
transaction log. All changes to the database are written to the log when
you commit. Those changes are written to the data files only later. To
reduce the size of the log, schedule a log backup job periodically (every 15
or 30 minutes on busy systems is not uncommon).

David
 
Back
Top