I have a script that executes 15 bulk insert operations into a SQL Server Database sequentially, all based on the one below:
BULK INSERT M_USERS
FROM '\\spath\users.txt'
WITH (
BATCHSIZE = 1000,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 2,
ERRORFILE='\\spath\users.err'
)
GO
The 15 text files vary in size. Some have less than 5000 rows, while the largest have over 5,000,000 rows. I am running the script on a server with 12 GB of memory and a page file of 24GB. Once it starts, I notice the physical memory usage on that server steadily increases until in nears 100%. Even after the script completes (or fails due to lack of memory) the memory usage never drops. It seems that Sql Server never releases the memory resources it uses to perform the bulk inserts.
Does anyone know if I could address this by revising my script, or is this an issue with SQL Server?
Edit: Eventually I get the error "There is insufficient system memory in resource pool 'internal' to run this query." and everything fails after that.