memory management in a 2005 Cluster on a 64bit platform

Question

Post reply

memory management in a 2005 Cluster on a 64bit platform

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jpotucek SSCoach Points: 18583 More actions · Answer 1

Ran this:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

sp_configure 'max server memory', 6144

RECONFIGURE

GO

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

Still only using 5GB of memory. Cycle the instance to take effect?? Not sure how to do that in a clustered environment.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

SQL Server needn't be recycled for changing the MAX Memory.

If using TSQL then the it should look like this

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'max server memory', 6144;

GO

RECONFIGURE;

GO

Also if there is no need for additional memory SQL Server will not use more memory. It will be just using what it needs i.e 5GB. Setting max = 6GB doesn't mean that it should always use 6GB. Even if it aquires 6GB for its needs, once the task is done, SQL Server will release additional memory back to the OS.

Please try to run the DTS package and check if you are getting the same error again.

Thank You,

Best Regards,

SQLBuddy

Jpotucek SSCoach Points: 18583 More actions · Answer 3

received the same error. The error is on a bulk insert - with transformations - into a table from a 1.6GB text file.

We are going to try it tomorrow with a different weekly .txt file. keep you posted and thanks for your help.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

I think everyone is focusing on the wrong thing here. I think it is the SSIS/DTS engine that needs the memory and that INCREASING sql server memory AND having locked pages for SQL Server is CAUSING the problem. Either get rid of locked pages and reduce max memory during load time or move load processing to the standby cluster machine or both and see what happens.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jpotucek SSCoach Points: 18583 More actions · Answer 5

I found out what the problem was. Bad input file!! got a new txt file this week and it ran through fine.

took the old file and a collegue of mine tried to 'tail' the last bit of the original file with CYGWIN and it went into a serious loop. the end of file character must have gotten corrupted. go figure.

THank you for all your help. it was a learning experience. I now know much more about how sql server handles memory on a 64bit machine : )