cancel
Showing results for 
Search instead for 
Did you mean: 

Full Optimize Failing

Former Member
0 Kudos

Hello,

Was wondering if any experts can give any insite into how to fix our full optimize from failing. We have had a custom package running for a few years which one of the last steps it does a full optimize. About a week ago it started to fail on this step and when we try to manually optimize from the front end it fails as well. We are able to optimize from the backend however. There are no additional processing running during this time, can anyone give any suggestions on what may be causing the problem? Is there some temp file that needs to be cleaned? I tried to do a modify of the application and that has not worked. We are on version 7.0.

Thanks in advance!

Kevin

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Kevin,

Have you compressed your database using the FULL optimize with Compress Database before?

Possibilities are;

1) if you have a lot of records in fact table.

2) if you have no space for db log files or temp db or temp db log files.

Most probably, your issue must be related to space inadequacy.

Also, check for dirty records in the fact tables.

Hope this helps.

Karthik AJ

Former Member
0 Kudos

Yes, we us the full optimize with compress all that time. That is when is always failing nbow. I can do the full optimize with re-indexing.

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Kevin,

Can you check the TblLogs table in the AppServer database for any errors?

Use this query:

USE AppServer

SELECT * FROM TBLLOGS WHERE DATEWRITTEN BETWEEN '07/26/2011 10:00 AM' and '07/27/2011 10:00 AM'

Remember to update the dates if they are incorrect.

Thanks,

John

Former Member
0 Kudos

can you tell us what error you are getting when you optimize through the admin console?

Thanks.

Former Member
0 Kudos

It doesn't really give me an error. It just fails. I am going to try arcive some data from our fact tables and see if that helps. The regular optimize is working, it's just when we select the optimize with compress.

Former Member
0 Kudos

I am also taking a look at that table to see if there is anything in there.

Thanks for the help by the way!

Former Member
0 Kudos

Here are a few of the messgaes I see in that table around the time of me trying to optimize this morning. Not sure what these mean:

==============[System Error Tracing]============== [System Name] - ApplicationService [Message Type] - ErrorMessage [Job Name] - OSoft.Services.Webservice.ApplicationService.ApplicationService.Query() [DateTime] - 7-27-2011 7-29-05 AM [UserId] - [Exception] DetailMsg - {You are not authorized to perform this task. Please contact your BPC administrator.} ==============[System Error Tracing End ]==============

==============[System Error Tracing]============== [System Name] - ApplicationService [Message Type] - ErrorMessage [Job Name] - OSoft.Services.Webservice.ApplicationService.ApplicationService.Query() [DateTime] - 7-27-2011 7-29-16 AM [UserId] - [Exception] DetailMsg - {You are not authorized to perform this task. Please contact your BPC administrator.} ==============[System Error Tracing End ]==============

==============[System Error Tracing]============== [System Name] - DatabaseHandler [Message Type] - ErrorMessage [Job Name] - CommitTran [DateTime] - 7-27-2011 7-37-54 AM [UserId] - User [SqlException] Type - System.Data.SqlClient.SqlException Errors - System.Data.SqlClient.SqlErrorCollection Class - 10 LineNumber- 0 Message - {Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.} Number - -2 Procedure - ConnectionRead (recv()). Server - BPCMS7P-OMF-03 State - 0 Source - .Net SqlClient Data Provider TargetSite- Void OnError(System.Data.SqlClient.SqlException, System.Data.SqlClient.TdsParserState) HelpLink - ==============[System Error Tracing End ]==============

==============[System Error Tracing]============== [System Name] - DatabaseHandler [Message Type] - ErrorMessage [Job Name] - RollbackTran [DateTime] - 7-27-2011 7-37-55 AM [UserId] - User [Exception] DetailMsg - {This SqlTransaction has completed; it is no longer usable.} ==============[System Error Tracing End ]==============

==============[System Error Tracing]============== [System Name] - OSoftOptimizeManage [Message Type] - ErrorMessage [Job Name] - OptimizeApplication() [DateTime] - 7-27-2011 7-37-55 AM [UserId] - mscorlib [Exception] DetailMsg - {Exception has been thrown by the target of an invocation.} ==============[System Error Tracing End ]==============

==============[System Error Tracing]============== [System Name] - ApplicationService [Message Type] - ErrorMessage [Job Name] - OSoft.Services.Webservice.ApplicationService.ApplicationService.Query() [DateTime] - 7-27-2011 7-40-18 AM [UserId] - [Exception] DetailMsg - {You are not authorized to perform this task. Please contact your BPC administrator.} ==============[System Error Tracing End ]==============

==============[System Error Tracing]============== [System Name] - ApplicationService [Message Type] - ErrorMessage [Job Name] - OSoft.Services.Webservice.ApplicationService.ApplicationService.Query() [DateTime] - 7-27-2011 7-40-45 AM [UserId] - [Exception] DetailMsg - {You are not authorized to perform this task. Please contact your BPC administrator.} ==============[System Error Tracing End ]==============

Former Member
0 Kudos

This seems to be the error for the optimize task:

==============[System Error Tracing]============== [System Name] - OSoftOptimizeManage [Message Type] - ErrorMessage [Job Name] - OptimizeApplication() [DateTime] - 7-27-2011 8-38-02 AM [UserId] - mscorlib [Exception] DetailMsg - {Exception has been thrown by the target of an invocation.} ==============[System Error Tracing End ]==============

Former Member
0 Kudos

These three messages seem consistant when the optimize is run:

1) CommitTran User 0 37:55.0 ==============[System Error Tracing]============== [System Name] - DatabaseHandler [Message Type] - ErrorMessage [Job Name] - CommitTran [DateTime] - 7-27-2011 7-37-54 AM [UserId] - User [SqlException] Type - System.Data.SqlClient.SqlException Errors - System.Data.SqlClient.SqlErrorCollection Class - 10 LineNumber- 0 Message - {Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.} Number - -2 Procedure - ConnectionRead (recv()). Server - BPCMS7P-OMF-03 State - 0 Source - .Net SqlClient Data Provider TargetSite- Void OnError(System.Data.SqlClient.SqlException, System.Data.SqlClient.TdsParserState) HelpLink - ==============[System Error Tracing End ]==============

2) RollbackTran User 0 37:55.0 ==============[System Error Tracing]============== [System Name] - DatabaseHandler [Message Type] - ErrorMessage [Job Name] - RollbackTran [DateTime] - 7-27-2011 7-37-55 AM [UserId] - User [Exception] DetailMsg - {This SqlTransaction has completed; it is no longer usable.} ==============[System Error Tracing End ]==============

3) OptimizeApplication() mscorlib 0 37:55.0 ==============[System Error Tracing]============== [System Name] - OSoftOptimizeManage [Message Type] - ErrorMessage [Job Name] - OptimizeApplication() [DateTime] - 7-27-2011 7-37-55 AM [UserId] - mscorlib [Exception] DetailMsg - {Exception has been thrown by the target of an invocation.} ==============[System Error Tracing End ]==============

Former Member
0 Kudos

I would check the 3 fact tables to ensure that the member id's match up with the mbr tables.

This should not be a very difficult test to accomplish. If you recently deleted a member which still has data or you renamed a member without using NEWID then this could cause a optimisation error.

However usually if there is an OLAP error it will be recorded in error logs + event viewer.

Have you tried to process the application - try click application> modify> select process> ok

You might also try this common solution:

Open Control Panel -> Administrative Tools > Component Services

Expand Component Services > Computers > My Computer > COM + Applications

Stop and start the OsoftAdminServer COM+ object

Edited by: Hiren Amin on Jul 27, 2011 10:04 PM

Former Member
0 Kudos

Hi Kevin,

Please check if the security settings for the userid used for optimizing the application has been changed.

You may restart all the SQL Services and then the OSoft SendGovernor service.

Then try doing a full optimize again.

Also, as I have mentioned earlier, please check for dirty records.

Check for fact records with dimension members not in those dimensions and source column not as 0.

Hope this helps.

Karthik AJ

Former Member
0 Kudos

We have tried doing a modify application, restarting the server, bouncing the COM objects, the security is correct and the table ID's appear to all be fine. I'm working on archiving old data and keeping my fingers crossed hoping that is the issue. Thank you for the responses, I will keep you posted.

Edited by: Kevin Gontasz on Jul 28, 2011 1:35 PM

Former Member
0 Kudos

Ok, I have archive a ton of data and am still having the issue with the Optimize with compress failing. Does anyone have any other suggestions?

Former Member
0 Kudos

Hi Kevin,

If the full optimize works, then the data should be fine.

I still think the problem might be because no space for the db log files.

Please ensure you have shrunk the log file for the databases.

Hope this helps.

Karthik AJ

Former Member
0 Kudos

The database files seem to have plenty of space on the server. Do you know if doing an optimize from the backend compresses the database? Yes, the full optimize is working but the compress isn't, I'm afraid that we could see a major hit with performance if we are unable to compress the database.

Thanks,

Kevin

Former Member
0 Kudos

Kevin -

I have seen Compress fail in cases where there are bad records in the fact table. All it takes is one bad record. I have seen this where someone "promoted" a base member to a parent (by adding another base member to a dimension file and listing a base member as that member's ParentHx, OR added a FormulaHx column to a dimension and then entered a formula for a base member (which then by definition should no longer have base members in the fact table).

Please do a deep dive scrub of your Fact table records and verify that there are no bad records in your fact table. There should be no rows in your fact tables that contain members that are not BASE members.

One approach to do this is to make a copy of your fact tables and do an outer join on that copy to the BASE members(only) in (a copy of )your dimension files (no members containing dimension formulas and no members where CALC =Y - you could use an EVDRE to quicly generate the needed base members for each dimension). Repeat - there should be no rows in your fact tables that include members in any of the columns that are not BASE members.

Best regards,

Greg

Former Member
0 Kudos

We did a thorough scrub of the fact tables and there are no parent records.

Thanks fo the suggestion.

kevin

Former Member
0 Kudos

Ok, so we figured out what was going on with our opimize with compress failing. Our DBA's have mirroring turned on for this database. Once we turned the mirroring off the optimize with compress worked. We have this turned on in a few of our other BPC instances and don't have a problem. This instance in question is a bit larger of a data set but not sure why that would matter. Has anyone come across this situation?

Thanks,

Kevin

Former Member
0 Kudos

We had the same thing happen. Full optimize with compress started failing. I have always suspected the mirroring may have been the cause since we started the mirror about the same time. Do you still have the mirroring turned off?

Former Member
0 Kudos

Yes it is turned off. We have other servers where the mirroring is turned on and dont have an issue. So we have not figured out why it's an issue on this one server.

Answers (1)

Answers (1)

Former Member
0 Kudos

I figured it out