Problem with insert commit

[ny2292000]

I need someone who knows the subtleties of C# (VS2008, .net framework 3.5) and MSSQL2005
I am inserting info into a table using this writeforme method. It works but it seems that the transactions are never committed, that is, sqlserver exe uses more and more virtual memory. That is only written when I turn on and off the server

Am I doing something wrong

Thanks

Marc


public void WriteForMe(string s

SqlConnection connection = new global::System.Data.SqlClient.SqlConnection()
connection.ConnectionString = global::Trader.Properties.Settings.Default.Hedge_QuotesConnectionString
SqlCommand cmd = new SqlCommand()
cmd.Connection = connection
using (connection

using (cmd

cmd.CommandText = s
tr

if (cmd.Connection.State == System.Data.ConnectionState.Closed) { cmd.Connection.Open();
cmd.ExecuteNonQuery()

catch (SqlException se

se.Data.Clear()

finall

if (cmd.Connection != null

cmd.Connection.Close()

 

[Alberto Poblacion]

I need someone who knows the subtleties of C# (VS2008, .net
framework 3.5) and MSSQL2005.
I am inserting info into a table using this writeforme method. It works
but it seems that the transactions are never committed, that is, sqlserver
exe uses more and more virtual memory. That is only written when I turn on
and off the server.

Am I doing something wrong?

Your code seems to be correct. Since you are not explicitly opening a
transaction, the server will implicity use a transaction for every query
that you run with ExecuteNonQuery, which gets immediately committed by
default.

The server using more and more memory is unrelated to transactions. This
hapens when the server keeps caching pages from the database file on disk
into the cache buffer in memory. The more pages that are read from disk, the
more memory is used to keep them in cache. You can limit the memory that Sql
Server uses for this purpose; it can be changed from the Properties window
of the server in Sql Server Management Studio.

 

[Marco]

Thanks. I was surprised that this happened since another sequence of calls from a multi-threaded section of the program did not generated this behavior. In the single-threaded section this cache reaches 1.5 gigbytes in a day

 

[Marco]

I have a question. Why is SQLServer caching insert commands? I can see a reason for caching queries but not for inserts

Anyone has any comments. Thanks

 

[Alberto Poblacion]

I have a question. Why is SQLServer caching insert commands? I can see a
reason for caching queries but not for inserts.

It caches any page that is read or written from the data file on disk.
This means that all pages that contain the data from the inserts are cached,
as well as all the index pages that are affected, either because they are
read (for instance, to verify a unique constraint or a primary key, or to
insert the record in a clustered index) or because they are written (the
index is modified when new records are inserted). Every page that is read or
written is kept in the cache, in case that it needs to be read later on.

 

[Marco]

Thanks a million, Alberto. It is finally clear to me why

Marco