Memory leak? and dates....

[Gizz]

Hi
I have recently installed SQL Server 7
I'm an NT and SQL newbie, so be gentle...
After 2 or 3 days the memory used by SQL is up in the 190MB region from 100MB startup (the actuall app database is still tiny) and NT comes up with a 'run out of virtual memory' error/message.
Why is SQL bloating? I'm not exactly doing much with it, and my DB apps are closing down properly (no recordsets / connections left open).
Help help help!

Also, if I have a date in a string format, whats the easiest way to get SQL to compare it to a date type in a where clause???? :-)

Thanks all.

PIII 256MB NT Server 4 option pack 4 service pack 4

Check under current activity in Enterprise Manager to see if there are any processes running that are taking up the memory. I haven't noticed any significant memory leaks anywhere, but I have been caught by bad queries and some IIS bugs that act like a Denial Of Service attack by eating up all of the resources.

You can attempt to kill the process if you see it running, but may have to reboot the server if you can't stop it. This is not uncommon.

As for the date, there are 2 useful functions you should look into.

datediff(parm1,parm2,parm3) -- allows you to compare dates
getdate() -- is the current date/time

You can compare 2 dates with datediff based on almost anything (hours apart, days, months, years, weeks, milliseconds etc)

Simple examples of comparing dates:

Select * From MyTable Where Date1 < Date2
Select @ From MyTable Where DateDiff(mm,date1,getdate()) < 1 -- where date1 is less than a month old

------------
Gizz at 1/4/00 5:11:49 AM

Hi
I have recently installed SQL Server 7
I&#39;m an NT and SQL newbie, so be gentle...
After 2 or 3 days the memory used by SQL is up in the 190MB region from 100MB startup (the actuall app database is still tiny) and NT comes up with a &#39;run out of virtual memory&#39; error/message.
Why is SQL bloating? I&#39;m not exactly doing much with it, and my DB apps are closing down properly (no recordsets / connections left open).
Help help help!

Also, if I have a date in a string format, whats the easiest way to get SQL to compare it to a date type in a where clause???? :-)

Thanks all.

PIII 256MB NT Server 4 option pack 4 service pack 4

[Gizz]

Hmmm...
Thanks for the date stuff - useful.
However, still got probs with SQL Server.
Looking at the SQL Server process in the process manager, it increases its memory taken by 12K or so every time I run a simple select statement. Is it building a temporary table? (tempdb????) If so, will it stop? Should I delete something to stop the bloat? It really can&#39;t be realistic to get a 4MB/minute memory leak as standard??? (I call a select statement every 10 seconds from a VB app to check if the DB has something to do - cr*p, yes, but still valid...)
Help, anyone?


------------
at 1/4/00 4:16:58 PM

Check under current activity in Enterprise Manager to see if there are any processes running that are taking up the memory. I haven&#39;t noticed any significant memory leaks anywhere, but I have been caught by bad queries and some IIS bugs that act like a Denial Of Service attack by eating up all of the resources.

You can attempt to kill the process if you see it running, but may have to reboot the server if you can&#39;t stop it. This is not uncommon.

As for the date, there are 2 useful functions you should look into.

datediff(parm1,parm2,parm3) -- allows you to compare dates
getdate() -- is the current date/time

You can compare 2 dates with datediff based on almost anything (hours apart, days, months, years, weeks, milliseconds etc)

Simple examples of comparing dates:

Select * From MyTable Where Date1 < Date2
Select @ From MyTable Where DateDiff(mm,date1,getdate()) < 1 -- where date1 is less than a month old

------------
Gizz at 1/4/00 5:11:49 AM

Hi
I have recently installed SQL Server 7
I&#39;m an NT and SQL newbie, so be gentle...
After 2 or 3 days the memory used by SQL is up in the 190MB region from 100MB startup (the actuall app database is still tiny) and NT comes up with a &#39;run out of virtual memory&#39; error/message.
Why is SQL bloating? I&#39;m not exactly doing much with it, and my DB apps are closing down properly (no recordsets / connections left open).
Help help help!

Also, if I have a date in a string format, whats the easiest way to get SQL to compare it to a date type in a where clause???? :-)

Thanks all.

PIII 256MB NT Server 4 option pack 4 service pack 4

SQL creates temporary tables based on the complexity of your queries. No they do not stay there forever and they are also not resident in memory but are physical files on disk. They are killed once they&#39;re used unless you explicitly create them and intend for them to stay.

What is this select statement that you are running?

Ditch the VB app until you locate the problem as well. You should start with the server itself and not have some application factoring in to the equation until you can be sure the problem is not with SQL Server.

I highly doubt that you are experiencing this memory leak because of SQL, unless you have done some interesting things with creating your own cursors / temp tables that you haven&#39;t removed / deallocated or you are working with a corrupt install.

My only other thoughts, short of the VB app that I trully suspect, is that you may not have the most updated service pack / drivers installed.


------------
Gizz at 1/5/00 6:03:26 AM

Hmmm...
Thanks for the date stuff - useful.
However, still got probs with SQL Server.
Looking at the SQL Server process in the process manager, it increases its memory taken by 12K or so every time I run a simple select statement. Is it building a temporary table? (tempdb????) If so, will it stop? Should I delete something to stop the bloat? It really can&#39;t be realistic to get a 4MB/minute memory leak as standard??? (I call a select statement every 10 seconds from a VB app to check if the DB has something to do - cr*p, yes, but still valid...)
Help, anyone?


------------
at 1/4/00 4:16:58 PM

Check under current activity in Enterprise Manager to see if there are any processes running that are taking up the memory. I haven&#39;t noticed any significant memory leaks anywhere, but I have been caught by bad queries and some IIS bugs that act like a Denial Of Service attack by eating up all of the resources.

You can attempt to kill the process if you see it running, but may have to reboot the server if you can&#39;t stop it. This is not uncommon.

As for the date, there are 2 useful functions you should look into.

datediff(parm1,parm2,parm3) -- allows you to compare dates
getdate() -- is the current date/time

You can compare 2 dates with datediff based on almost anything (hours apart, days, months, years, weeks, milliseconds etc)

Simple examples of comparing dates:

Select * From MyTable Where Date1 < Date2
Select @ From MyTable Where DateDiff(mm,date1,getdate()) < 1 -- where date1 is less than a month old

------------
Gizz at 1/4/00 5:11:49 AM

Hi
I have recently installed SQL Server 7
I&#39;m an NT and SQL newbie, so be gentle...
After 2 or 3 days the memory used by SQL is up in the 190MB region from 100MB startup (the actuall app database is still tiny) and NT comes up with a &#39;run out of virtual memory&#39; error/message.
Why is SQL bloating? I&#39;m not exactly doing much with it, and my DB apps are closing down properly (no recordsets / connections left open).
Help help help!

Also, if I have a date in a string format, whats the easiest way to get SQL to compare it to a date type in a where clause???? :-)

Thanks all.

PIII 256MB NT Server 4 option pack 4 service pack 4