Solution for Appstack and writable volume attachment getting failed – VMware AppVolume

I am trying to put down the issues and there resolutions which comes up during daily activities that will help me and also save someone’s day.

So lets start….

 Issue:   Appstack and writable volume attachment getting failed.

I started getting calls of user’s saying they are not getting Appstacks and Writable Volumes are also not getting attached.

So the first thing came in mind to check the VMware Appvolume Manager Logs, so logged into to the Web of Appvolume Manager and went to activity tab  and then checked the System logs and below is the snippet of the logs.

Logs checked:
Appvolume Manager System logs:

Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Unable to establish connection to Hypervisor <VMware ESX#25905900 root@esxi1.virtgeek.local>
Apr 02 2019 10:43PM
Failed to connect to vSphere at “root@esxi1.virtgeek.local”: SSL_connect returned=1 errno=0 state=error: certificate verify failed
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Unable to establish connection to Hypervisor <VMware ESX#25905900 root@esxi1.virtgeek.local>
Apr 02 2019 10:43PM
Failed to connect to vSphere at “root@esxi1.virtgeek.local”: SSL_connect returned=1 errno=0 state=error: certificate verify failed
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Unable to locate “Machine <VDIVM-02> (50048335-d1cc-e853-78f0-2f494c06d9b0)” on “esxi1.virtgeek.local” — Using default hypervisor instead

I was frequently seeing a single Esxi host i.e. esxi1.virtgeek.local with error Rejected because certificate is neither verified nor trusted.

Now we have seen the issue and also the logs, so it’s time to get it resolved as quickly as possible.

Here is what i did to get it resolved :

1) First check the credential which is given in Appvolume Manager for ESXi host and Actual Credential of ESXi host is same. If not you have to change the password of ESXi.

You need to check the credential of the hosts which is coming up in the logs.

2) If credentials are same for host in Appvolume Manager and ESXi host, go to next step.
3) Login to Appvolume webpage
4) Go to Configuration Tab –> Machine Managers —> Expand vCenter and Click on Edit.
5) Uncheck Mount on host and mount on local.
6) Enter vCenter credentials and click on Save.
7) Wait for couple of Minutes
8) Again go to Machine Managers, click on Mount on host and give ESXi credentials and for vCenter give vCenter credential.
Woooohoooo !!!!! The issue should get resolved with this.

You owe me a beer 🙂 if this helps you someday 😉 

If you want to know what caused it to resolve, please read below :

Unchecking the mount on host will remove the host certificate from appvolume database and thereafter again checking the mount on host option will update the host certificates in the database and thereafter appvolume will start proper communication with the host and this helped to resolve the problem.

I would be more than happy to reply to your queries.

Rajesh Poojary

Where to find me 😉

Twitter : @RajjeshPoojary

Mail : Rajjesh.poojary@gmail.com

 

Steps to reduce or shrink vCenter DB Size

Recently i came up with the issue which was causing the vCenter DB (SQL DB) to grow huge, after digging on the internet and spending some time, i was able to find the cause and the solution for my vCenter DB size.

I am making this for the easy future reference , incase if faced same issue again and this will also help the other VMware Admins who are also facing the similar issue in their environment.

Environment: 

  1. Windows based vCenter 6.0 with external SQL DB
  2. SQL DB 2012 Standard

Issue:  vCenter DB size has grown huge more than expected and need to reduce the vCenter DB size.
Analysis: VPX_HIST_STAT tables has grown huge and that includes the orphaned VPX_HIST_STAT tables.

This may be happen if you have upgraded the vCenter to the new build.
VMware KB Article Followed:
https://kb.vmware.com/s/article/2058670
https://kb.vmware.com/s/article/1036738

The Script mentioned below are present in the above mentioned KB article.

This is very very very important thing to be noted

Note : Before doing any changes to the database, Please ensure you take the backup of the database on which you are working and here you need to make sure you have taken the latest vCenter Database backup.

If you want to know how to take the SQL DB backup:

  1. Login to the SQL Management Studio
  2. Right Click on the vCenter Database –> Go to Tasks–> Backup
  3. Browse the path to where you want to save the backup and give the filename to the backup file with .bak at the end of the filename . For e.g. vcenterdbbackup.bak

Steps to be followed to reduce the SQL DB size:

Note: This SQL Query may take a long time to complete the query execution depending upon the number of orphaned VPX_HIST_STAT tables and VPX_HIST_STAT tables which has grown huge to be deleted.

For me it took around 4 hours to complete the query execution
1) Stop the vCenter Service
2) Login to the SQL DB server and run the below SQL Query on vCenter Database
Note :SQL Query Starts from” /*” and ends at “SET NOCOUNT OFF”

You need to login to the SQL Management Studio, Select the vCenter Database and run the below mentioned SQL Query.

=====================================================================
/*
This script will delete data from VPX_HIST_STAT* tables which have no parent record in
corresponding VPX_SAMPLE_TIME* table.

You are strongly advised to shut down the VirtualCenter server and make
a complete backup of your database before running this script.

VirtualCenter Server must be stopped while this script is running.

Configurable parameters:
@delete_data – Set @delete_data to 1 to delete orphan rows.
In case of delete_data <> 1 the script will only count how many rows are orphaned
@delete_data must be 1 to actually delete rows.
@batch_size – Number of rows to delete per one transaction

The script may be executed via SQL Server Management Studio or from command line.

USER which executes the script must have privileges to delete data from vCenter Server database and
the user must have default schema equal to the vCenter Server database schema.

*/

SET NOCOUNT ON
— Create temporary tables
— #ORPHAN_STATS – contains table name and time_id which should be deleted
— #DYNAMIC_SQL – contains sql scripts which be executed later
IF OBJECT_ID(‘tempdb..#ORPHAN_STATS’) IS NOT NULL
DROP TABLE #ORPHAN_STATS
GO
CREATE TABLE #ORPHAN_STATS (table_name VARCHAR(30) NOT NULL, time_id BIGINT NOT NULL, deleted_rows BIGINT)

IF OBJECT_ID(‘tempdb..#DYNAMIC_SQL’) IS NOT NULL
DROP TABLE #DYNAMIC_SQL
GO
CREATE TABLE #DYNAMIC_SQL (orphan_sql varchar(2000) NOT NULL)

DECLARE @delete_data BIT
DECLARE @batch_size INTEGER
DECLARE @affected_rows INTEGER

print ‘*********************************************************************’
print ‘* This script is for deleting data from VPX_HIST_STAT tables which *’
print ‘* have not master record in corresponding VPX_SAMPLE_TIME table. *’
print ‘*********************************************************************’

SET @delete_data = 1
SET @batch_size = 10000

IF @delete_data = 1
PRINT ‘*** DATA WILL BE DELETED ! ***’
ELSE
BEGIN
PRINT ‘* Currently script only counts how many records will be deleted. *’
PRINT ‘* set @delete_data to 1 in order to actually delete rows *’
END
PRINT ‘*********************************************************************’
— Populate #DYNAMIC_SQL with scripts for collect orphan rows
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time1 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat1%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time2 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat2%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time3 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat3%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time4 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat4%’

DECLARE @l_orphan_sql VARCHAR(2000)
DECLARE c_exec_sql CURSOR FOR
SELECT orphan_sql FROM #DYNAMIC_SQL

OPEN c_exec_sql
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql

SET @affected_rows = 0
–Populate #ORPHAN_STATS with table names, time_id and count of rows for delete
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #ORPHAN_STATS
EXEC (@l_orphan_sql)
SET @affected_rows = @affected_rows+ @@ROWCOUNT
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
END
CLOSE c_exec_sql
DEALLOCATE c_exec_sql

SELECT CAST(SUM(deleted_rows) AS VARCHAR) + ‘ rows will be deleted from table ‘ + table_name
FROM #ORPHAN_STATS
GROUP BY table_name

IF @affected_rows > 0
BEGIN
IF @DELETE_DATA = 1
BEGIN
DECLARE c_exec_sql CURSOR FOR
SELECT orphan_sql FROM #DYNAMIC_SQL
— Populate tamp table #DYNAMIC_SQL with scripts for DELETE
TRUNCATE TABLE #DYNAMIC_SQL
INSERT INTO #DYNAMIC_SQL
SELECT ‘DELETE TOP (‘ + cast (@batch_size as varchar) + ‘) FROM ‘ + table_name + ‘ WHERE time_id = ‘ + cast (time_id as varchar) FROM #ORPHAN_STATS
OPEN c_exec_sql
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
WHILE 1=1
BEGIN
BEGIN TRAN
EXEC (@l_orphan_sql)
SET @affected_rows = @@ROWCOUNT
COMMIT TRAN

PRINT ‘EXECUTED SQL: ‘ + @l_orphan_sql
PRINT ‘Affected rows: ‘ + cast( @affected_rows as varchar)
PRINT ‘——————‘

IF @affected_rows < @batch_size BREAK
END
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
END

CLOSE c_exec_sql
DEALLOCATE c_exec_sql
END
END
ELSE
PRINT ‘There are not any orphan rows.’

SET NOCOUNT OFF

Ohh so the query execution completed 🙂

Now here we are to the last step, we have to run one more query that will shrink the database as we have deleted VPX_HIST_STAT tables. 

3) SQL query to Shrink the Database:

You need to login to the SQL Management Studio, Select the vCenter Database and run the below mentioned SQL Query.

SQL Query :
dbcc shrinkdatabase ( vcdbname , 20);
go
Note: When DBCC SHRINKDATABASE is specified with target_percent, the Database Engine calculates the target size to be thetarget_percent amount of space free in the file after shrinking.

And here we goooooo… You should have reduced the vCenter DB Size & create some free space in your drive and now you can start the vCenter service.

Enjoyyyyyy 🙂

Again Thanks to the VMware KB article to helping in resolving the issue 🙂

I would be more than happy to reply to your queries.

Rajesh Poojary

Where to find me 😉

Twitter : @RajjeshPoojary

Mail : Rajjesh.poojary@gmail.com