Pentaho Kettle Error: Could not create the Java Virtual Machine.

 E:\pdi\data-integration>Spoon.bat

DEBUG: Using JAVA_HOME

DEBUG: _PENTAHO_JAVA_HOME=C:\Program Files (x86)\Java\jdk1.7.0_65

DEBUG: _PENTAHO_JAVA=C:\Program Files (x86)\Java\jdk1.7.0_65\bin\javaw.exe

E:\pdi\data-integration>start "Spoon" "C:\Program Files (x86)\Java\jdk1.7.0_65\bin\javaw.exe"  "-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=256m" "-Dhttps.protocols=TLSv1,TLSv1.1,TLSv1.2" "-Djava.library.path=libswt\win32" "-DKETTLE_HOME=." "-DKETTLE_REPOSITORY=" "-DKETTLE_USER=" "-DKETTLE_PASSWORD=" "-DKETTLE_PLUGIN_PACKAGES=" "-DKETTLE_LOG_SIZE_LIMIT=" "-DKETTLE_JNDI_ROOT=" -jar launcher\pentaho-application-launcher-6.0.1.0-386.jar -lib ..\libswt\win32

Error :

Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

scenario :

Few hours before pentaho was running fine on machine, after changing the version of libraries, bat files and plugins i was facing above error.

So i was pretty sure about error is not related to OS version or java

Solution:

I decreased the Xmx1024m to Xmx512m in spoon.bat, it worked

Writing in Excel Using Excel Writer Step

 Failing During Writing in Excel while writing in excel , scenario can be as below

• 1 Transformation
• Multiple Excel Writer step
• All Excel Writer Step trying to write in 1 excel

What i noticed due to time or speed, if 1st step locked the excel to writing then 2nd excel writer step will failed to write. Which lead to fail whole transformation

How you can save yourself from your error, some how i found this after google it:

// for compatibility with Kettle 3.2 and 4.0: choose correct flag
var STATUS_FINISHED;
var STATUS_STOPPED;
var STATUS_HALTED;
var STATUS_HALTING;
if(STATUS_FINISHED == null) {
	try {
		// Kettle 3.2 flag
		STATUS_FINISHED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_FINISHED;
		STATUS_HALTING = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_HALTING;
		STATUS_HALTED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_HALTED;
		STATUS_FINISHED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_FINISHED;
		STATUS_STOPPED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_STOPPED;
	} catch(e) {
		// Kettle 4.0 flag
		STATUS_FINISHED = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_FINISHED;
		STATUS_HALTING = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_HALTING;
		STATUS_HALTED = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_HALTED;
		STATUS_STOPPED = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_STOPPED;
	}
}
// this is the step that must complete before moving on
var waitForStep;
if(waitForStep == null) {
	waitForStep = _step_.getDispatcher().findBaseSteps("WritingObjectProperties").get(0);
}
// sleep until the step we wait for is done
while(waitForStep.getStatus() != STATUS_FINISHED && waitForStep.getStatus() != STATUS_STOPPED && waitForStep.getStatus() != STATUS_HALTING && waitForStep.getStatus() != STATUS_HALTED) {
	println("waiting for the step to finish");
	java.lang.Thread.currentThread().sleep(5);
}

Keep "Check" the checkboxinside step "Java Script"

See screenshot from reference 

Procedure | Compare Field Count and Distinct Count From a Database

 This procedure is for SQL Server, please take a idea and feel free to modify and use this procedure as per your need. This Procedure giving 3 fields in Output, 1st field giving column Name, 2nd field giving total count of records in table, and 3rd field having distinct records of a field.

CREATE TABLE #tempCount (
	TableName NVARCHAR(500)
	, AllTableCount NVARCHAR(500)
	, ColumnDistinctCount NVARCHAR(500)
	)

ALTER PROCEDURE countOfAField
AS
DECLARE @table_name NVARCHAR(50)
	, @column_name NVARCHAR(50)
	, @SQL_v NVARCHAR(500)

DECLARE cur_col CURSOR
FOR
SELECT TABLE_NAME
	, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'XXXXX'

OPEN cur_col

FETCH NEXT
FROM cur_col
INTO @table_name
	, @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL_v = 'Insert into #tempCount 
					Select 
						''' + @table_name + ''' as TableName
						, count(*) as AllTableCount 
						,count(distinct ' + @column_name + ') AS ColumnDistinctCount 
					from  ' + @table_name

	PRINT @SQL_v

	EXEC sp_executesql @SQL_v

	FETCH NEXT
	FROM cur_col
	INTO @table_name
		, @column_name
END

SELECT *
FROM #tempCount

CLOSE cur_col

DEALLOCATE cur_col

EXEC countOfAField