| Author |
Topic |
|
neo_6053
Starting Member
24 Posts |
 Posted - 2008-12-10 : 01:13:07
|
| I run codes to get all the columns for all my table in database using sqldatareader (VB.net). But then the sqlserv.exe use up too many memory. i tried to dispose and close everything at the end of the program but still not work. i've ask in other forum , some say i have to optimize the sql server. How can i do that? SQL server 2005 would not auto clean up the memory after used? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
neo_6053
Starting Member
24 Posts |
Posted - 2008-12-10 : 02:35:13
|
| OTL how can i fix it ?? no clue? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-10 : 03:16:30
|
Do you useSELECT * FROM INFORMATION_SCHEMA.COLUMNS I can't see how that would use all available memory.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
neo_6053
Starting Member
24 Posts |
Posted - 2008-12-10 : 03:48:59
|
| i'm actually running a loop to "select top 1 * from table" for all 400+ table ... i never thought of the memory issue when i started. there are some reason why i'm doing this as there are some functions running in the loop, such as comparison with ORACLE table and row count. (i'm actually trying to compare schema and records of ORACLE and SQL before migration)I think i should just redesign my program.thankx. But anyway, the sqlserv.exe wouldnt free up memory used by .Net even i close all the connection??? that's a strange thing. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-10 : 04:20:52
|
If you only want the columns names, and still use your approach, change "TOP 1" to "TOP 0".
E 12°55'05.63"N 56°04'39.26" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-10 : 09:26:54
|
| See SSMA for oracle in microsoft. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-12-10 : 10:16:20
|
quote:
Originally posted by neo_6053...But anyway, the sqlserv.exe wouldnt free up memory used by .Net even i close all the connection??? that's a strange thing.
Memory used by .NET, and memory used by SQL Server are two entirely separate pools of memory. How would freeing up .NET memory affect SQL Server's own allocations? |
|
|
|
neo_6053
Starting Member
24 Posts |
Posted - 2008-12-12 : 02:00:51
|
| ya, that's what i want to find out. How can i free up memory used by SQL? at 1st , i thought it's something to do with connection. But although i close it, the memory used still not decrease. |
|
|
|
neo_6053
Starting Member
24 Posts |
Posted - 2008-12-12 : 02:31:11
|
| this is my code . Dim sqlCOn As New SqlConnection("Server=localhost;Database=phxtest;Trusted_Connection=yes") Dim sqlda As SqlDataAdapter Dim sqldt As DataSet Dim sqlFieldCount As Integer sqlCOn.Open() Dim SqlCommand As New SqlCommand("select top 0 * from msaspot", sqlCOn) sqlda = New SqlDataAdapter(SqlCommand) sqlDt = New DataSet sqlDa.Fill(sqlDt) ## Here is where it use up the memory ## sqlDa.Dispose() sqlFieldCount = sqldt.Tables(0).Columns.Count sqldt.Clear() sqldt.Dispose() SqlCommand.Dispose() sqlCOn.Close() sqlCOn.Dispose() |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
sqlserv.exe use too much memory