Memory MGT on a 64bit 2005 Cluster

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Memory MGT on a 64bit 2005 Cluster

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-09-16 : 15:03:00

I am a SQL Server DBA with little to no experience on a Cluster OR with the 64bit platform.

Our new hosting company set up the Cluster for us and now that I've restored all of the Databases we are running into a problem with memory.

all of our Legacy DTS packages are working fine except one which blows with with this error when we try and run a Production size file into it:

DTS processed 744737 rows and blew up on 744738, with following error:
Error string:  Error at Source for Row number 744738. Errors encountered so far in this task: 1.

Error source:  DTS Data Pump

Error string:  Error creating file mapping view: Not enough storage is available to process this command

I'm thinking my memory is not allocated correctly.

This is a 2 node Cluster (active\passive) each node has 8GB of Memory. SQL Server 2005 64 bit on 64bit servers. MIN\MAX memory is set up at 1024mb min and 5120mb max. AWE is enabled and lock pages in memory is enabled. When I look in the task manager, the sqlserver service is using 5,481,592 k of memory.

Something does not seem right. I've been reading up on it but AWE and lock pages in memory on a 64bit platform is very confusing.

any recommendations???

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-16 : 15:07:08

sounds like a disk space or hardware issue rather than a memory issue.

as for memory, is the windows swap file properly configured?

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-09-17 : 08:59:05

swap file is set to 2GB

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-09-17 : 09:23:13

another thing I have to considered.. our DTS packages work fine with production volume data on our Production system which is sql server 2005 on 32bit. This new cluster is 64bit and I may need to install the Microsoft SQL Server 2005 Backward Compatibility Components from the article "The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS)"

any comments? has any ever done this?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-17 : 09:34:27

swap file should be 8GB.

we kept a SQL 2000 box around to run DTS packages until we got all of em migrated to SSIS.

or you can set up an application server with the SQL 2000 client tools just to run dtsrun.exe and save the packages as .dts files

if you must run them from your 2k5 box, good idea to d/l latest compatibility components, though i believe they are for editing the packages, not executing em.

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-09-17 : 12:31:47

also found this:

One problem I fouund is that SQL Server 2005 Standard Edition on a 64bit System does not support Lock Pages in Memory without CU 4 for Service Pack 3. Here is the article and a quote from the Article:
http://support.microsoft.com/kb/970070/
Microsoft SQL Server 2008 Standard Edition 64-bit systems or Microsoft SQL Server 2005 Standard Edition 64-bit systems that have the Lock Pages in Memory privilege set for the service account the SQL Server Engine do not use Address Windowing Extensions (AWE) APIs for buffer pool memory allocation. The use of AWE APIs on 64-bit editions of SQL Server for buffer pool memory allocation is
known as Locked Pages. Cumulative Update package 2 for SQL Server 2008 Service Pack 1 and cumulative update package 4 for SQL Server 2005 Service Pack 3 provide support for Locked Pages.

I'm not sure if I should install the post service pack cummulative update or if lock pages in memory not functioning even matters that much!!!?? Or maybe throw more memory at SQL Server?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-17 : 15:30:39

don't know if you need more memory or not. are you measuring PLE, Paging etc? I do know that your page file is too small.

what is result of SELECT @@VERSION?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources