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,

Marco

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;
try
{
if (cmd.Connection.State == System.Data.ConnectionState.Closed) { cmd.Connection.Open(); }
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
se.Data.Clear();
}
finally
{
if (cmd.Connection != null)
{
cmd.Connection.Close();
}
}
}
}
}

---
frmsrcurl: http://msgroups.net/microsoft.public.dotnet.languages.csharp/

[Alberto Poblacion]

"ny2292000" <us...@msgroups.net> wrote in message
news:eC%2319oYf...@TK2MSFTNGP04.phx.gbl...

> 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.

---
frmsrcurl: http://msgroups.net/microsoft.public.dotnet.languages.csharp/Problem-with-insert-commit

[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,

---
frmsrcurl: http://msgroups.net/microsoft.public.dotnet.languages.csharp/Problem-with-insert-commit

[Alberto Poblacion]

"Marco" <us...@msgroups.net/> wrote in message
news:%23OZcf3a...@TK2MSFTNGP02.phx.gbl...

>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

---
frmsrcurl: http://msgroups.net/microsoft.public.dotnet.languages.csharp/Problem-with-insert-commit