Cognos 10 cube building and memory

[mrmo]

Some advice please.

The server which hosts Transformer also hosts the MS-SQL database that holds the data. The server has 32gig of ram,

Currently sqlsevr.exe is holding on to 17,414,000k of memory and Transformer is unable to build a cube, complaining it has no memory when i try.

I know from experience that if i reboot the server it will work, but usually i don't have to do this twice in one week, I am trying to understand why there is a problem and what options there are that don't involve shutting down the server, and with it Cognos.

(I am in no position to move applications or get more ram so don't even suggest them as solutions)

[MFGF]

Some advice please.

The server which hosts Transformer also hosts the MS-SQL database that holds the data. The server has 32gig of ram,

Currently sqlsevr.exe is holding on to 17,414,000k of memory and Transformer is unable to build a cube, complaining it has no memory when i try.

I know from experience that if i reboot the server it will work, but usually i don't have to do this twice in one week, I am trying to understand why there is a problem and what options there are that don't involve shutting down the server, and with it Cognos.

(I am in no position to move applications or get more ram so don't even suggest them as solutions)

Hi,

Although SQL Server might be hanging on to 17Gb memory, it's likely something else is also using a lot of memory too. Transformer is a 32-bit client application, which means it can only grab a maximum of 2Gb memory per transformer.exe process. From what you are describing, there isn't enough free memory to allow Transformer to run, meaning either the OS has the remaining memory, or another process on that server is grabbing the memory you think should be available?

If your server has a total of 32Gb memory, how much is free when just the OS is up (ie if you don't start SQL Server)? How much is free after starting SQL Server? When Transformer won't run, how much free memory does the OS show is available? What are the processes using the memory other than SQL Server?

Cheers!

MF.

[mrmo]

currently the system has been up 15hours and won't build, so.

sqlservr.exe 17gig
java.exe 1.5gig
java.exe 400meg
java.exe *32 300meg
BIBusTKSeverMain.exe *32 300meg
BIBusTKSeverMain.exe *32 250meg
dtexec.exe 75meg
and dropping from there.

Resource Monitor has 22768mg in use 22meg modified, 35 hardware reserved, 755 reserved and 9190 free???

Is there anything I should be looking for?

[MFGF]

currently the system has been up 15hours and won't build, so.

sqlservr.exe 17gig
java.exe 1.5gig
java.exe 400meg
java.exe *32 300meg
BIBusTKSeverMain.exe *32 300meg
BIBusTKSeverMain.exe *32 250meg
dtexec.exe 75meg
and dropping from there.

Resource Monitor has 22768mg in use 22meg modified, 35 hardware reserved, 755 reserved and 9190 free???

Is there anything I should be looking for?

Looks like you are running your Cognos server instance on that machine too? 9190Mb free means 9Gb - should be more than enough to accommodate a Transformer process.

What exact error do you get when launching Transformer?

MF.

[mrmo]

Transformer will launch, I can open the project, I attempt a test build* and get

*The SSIS job that triggers the full build failed so thought I would try a test build to try and get an informative error message.

RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.
UDA-SOR-0001 Unable to allocate memory.
UDA-SOR-0001 Unable to allocate memory.
UDA-SOR-0001 Unable to allocate memory.

[MFGF]

Transformer will launch, I can open the project, I attempt a test build* and get

*The SSIS job that triggers the full build failed so thought I would try a test build to try and get an informative error message.

RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.
UDA-SOR-0001 Unable to allocate memory.
UDA-SOR-0001 Unable to allocate memory.
UDA-SOR-0001 Unable to allocate memory.

Hi,

You omitted to post a critical fact here - your Transformer build is using Cognos BI reports or packages as its data source, and you have Cognos BI running on the same server. I'm guessing your Transformer Build is firing off multiple reports to retrieve the data, and that these reports are written in a way that results in local sorting having to be performed on the Cognos server? The error you have posted is an indication that there is not enough free memory to accommodate the sort buffer for the Cognos BI reports. What is the sort buffer set to in Cognos Configuration for your BI server? If you have it set to 4Gb, and you have four reports running concurrently, then Cognos BI is going to try to grab 4 x 4Gb = 16Gb memory for local sorting.

I'd be looking to either reduce the sort buffer setting in Cognos Configuration, or else revise the underlying reports so they don't initiate local sorting on the Cognos server.

Cheers!

MF.

[mrmo]

build for this report, ssis sql job copies data between live and datawarehouse on server one.

the next step is that first job calls a ssis job on the second server which starts the transformer build.

The datasource was written in report studio, which runs on server two but references server one.

I hadn't even though about the full process just the cube not working bit

I have just inherited all this so just have to keep it working for now, the businesses long term plans mean putting lots of effort in, in the short term don't make a lot of sense.

Just gone in to IBM Cognos Configuration and I found an entry Sort Buffer size in MB, 4.

If I drop the sort buffer to 3, for example, will this have any meaningful impact? Is it likely to be detrimental? I am assuming this is the buffer you are thinking of?

[MFGF]

build for this report, ssis sql job copies data between live and datawarehouse on server one.

the next step is that first job calls a ssis job on the second server which starts the transformer build.

The datasource was written in report studio, which runs on server two but references server one.

I hadn't even though about the full process just the cube not working bit

I have just inherited all this so just have to keep it working for now, the businesses long term plans mean putting lots of effort in, in the short term don't make a lot of sense.

Just gone in to IBM Cognos Configuration and I found an entry Sort Buffer size in MB, 4.

If I drop the sort buffer to 3, for example, will this have any meaningful impact? Is it likely to be detrimental? I am assuming this is the buffer you are thinking of?

But it's only 4Mb. You could have 1000 reports running concurrently and total sort memory would still only be 4Gb.

Is it possible the sort memory isn't big enough? Is the report feeding data into Transformer sorting data? If so, does it really need to?

MF.

[mrmo]

thanks, As far as I can see no, if I run the query in Cognos Connection it is basically a list of orders, attributes and facts.

Any guidance as to where might be a good place to look within the model to find if there is a problem.

[mrmo]

one other issue, the source server is getting full, not THAT full, it still has 14gig of disk space, but would that cause an issue??

edit, I just checked the datawarehouse database and it is 17gig in size???? transformer won't reference ALL of that, but as a large chunk is transactions...