I know my problem has been discussed ad nauseum but I still don't get it so
please bear with me.
I have written a service which performs some work against a database once a
day (usually in the wee hours of the morning). From the time the service
starts to the first time it hits the database its memory consumption is
about 6.9MB which is a figure I can live with. However, after it hits the
database for the first time its memory jumps to 15MB and stays there. Now,
I'm a realist so 15MB isn't all that bad. I'm just looking to make the
service as slim as possible. so I would like, after it's finished with its
work if the memory could go back down to ~7MB. I'm wondering if there's a
better way to get rid of my database objects than what I'm doing now. Here's
the relevant portion of my service:
public class MyService: System.ServiceProcess.ServiceBase
{
private string timeToWork = "02:00";
private string startupVar2 = "something else";
private string configFileName = "MyService.cfg";
private System.Timers.Timer timer1 = null;
private EventLog appLog = null;
private SqlConnection sqlConnection = null;
private SqlDataAdapter da = null;
private DataTable dt = null;
public ETSReminder()
{
this.ServiceName = "MyService";
this.CanStop = true;
this.AutoLog = false;
appLog = new EventLog();
appLog.Source = "MyService";
}
// OnStart method starts the timer and adds the Elapsed event
// OnStop closes or disposes the service objects (
private void timer1_Elapsed(object sender,
System.Timers.ElapsedEventArgs e)
{
if (DateTime.Now.TimeOfDay >
Convert.ToDateTime(timeToWork).TimeOfDay &&
DateTime.Now.TimeOfDay <
Convert.ToDateTime(timeToWork).AddMinutes(5.5).Tim eOfDay)
{
DoWork();
}
}
private void DoWork()
{
try
{
ArrayList connectionStrings =
GetConnectionStrings(configFileName);
sqlConnection = new SqlConnection();
SqlCommand cmd1 = new SqlCommand("sproc1");
cmdCleanup.CommandType = CommandType.StoredProcedure;
cmdCleanup.Connection = sqlConnection;
SqlCommand cmd2 = new SqlCommand("sproc2");
cmdUserList.CommandType = CommandType.StoredProcedure;
cmdUserList.Connection = sqlConnection;
SqlCommand cmd3 = new SqlCommand("sproc3");
cmdReminderSent.CommandType = CommandType.StoredProcedure;
cmdReminderSent.Connection = sqlConnection;
foreach (string connectionString in connectionStrings)
{
sqlConnection.ConnectionString = connectionString;
sqlConnection.Open();
cmd1.ExecuteNonQuery();
da = new SqlDataAdapter(cmd2);
dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
// Does stuff including sending some email
cmd3.ExecuteNonQuery();
}
sqlConnection.Close();
}
}
catch
{
// Handle any exceptions
}
finally
{
dt.Dispose();
da.Dispose();
sqlConnection.Dispose();
}
}
}
---end code---
I have the database objects as class variables in case something hangs while
it's working, I can close the connection from my error handling routines.
I have even tried adding GC.Collect() after I dispose sqlConnection but that
doesn't seem to make the memory ever go down.
I have another more sinister problem in that each time the timer1_Elapsed
runs about 20K of memory is added that never goes away. Since this is a 24/7
service this could be an even bigger problem.
I have no ego over my code so if I'm doing something dumb please tell me.
Otherwise I would love to hear any ideas how I may solve my two memory
issues.
Thanks,
nautonnier
