I'm not sure whether this question belongs here or to StackOverflow. I'm attempting here, since my question is in regards of memory leaks and management stuff more than programming.
I have a SQL script which I attempt to run and everytime the server response is:
No sufficient memory to complete this query (that is the main idea, not the exact message)
Now, the script has more than 50 000 rows to insert like the example below:
insert into Cities ([Name]) values (N'MyCityName')
insert into Sectors ([Name], [Description], City_CityId)( select N'FirstSector', N'1at Sect. Desc.', c.CityId from Cities c where c.[Name] like N'MyCityName')
insert into Streets ([Name], Direction_Value, Type_Value, SectorId, City_CityId)( select N'1st Street', 0, 10, s.SectorId, c.CityId from Cities c inner join Sectors s on s.City_CityId = c.CityId where c.[Name] like N'MyCityName' and s.[Name] like N'FirstSector')
insert into Addresses (StreetNumber, NumberSuffix_Value, UnitSuiteAppt, StreetId, SectorId, CityId)( select 999, 0, N'', st.StreetId, s.SectorId, c.CityId from Cities c inner join Sectors s on s.City_CityId = c.CityId inner join Streets st on st.SectorId = s.SectorId and st.City_CityId = c.CityId where c.[Name] like N'MyCityName' and s.[Name] like N'FirstSector' and st.[Name] like N'1st Street')
insert into People (Surname, FirstName, IsActive, AddressId)( select N'TheSurname', N'TheFirstName', 1, a.AddressId from Addresses a inner join Cities c on c.CityId = a.CityId inner join Streets s on s.StreetId = a.StreetId where a.StreetNumber = 999 and a.NumberSuffix_Value = 0 and a.UnitSuiteAppt = N'' and c.[Name] like N'MyCityName' and s.[Name] like N'1st Street')
So, I have the number of each instruction as follows:
Executing those thousands of instruction will drive me to the unsufficient memory issue. And while I open Task Manager, I have SSMS necessitating more than 400MB of RAM.
My configuration is as stated below:
Lenovo W700ds 2x320GB HDD 7200RPM RAID 0 4GB RAM DDR3 Intel Core 2 Quad 2.0GHz 6MB L2 Windows 7 Professional 64bits (/w all updates) SQL Server 2005 Express services running (That is my data server, I'm not using 2008 for this project) SQL Server Management Studio 2008 Express (SP3 installed /w all updates)
I only have SSMS2008 application running while executing the inserts instructions.
Any thoughts to render this situation doable either by system optimization or other updates are greatly appreciated.