VCenter Server 4.1 Running under SQL Server pushes tempdb storage allocation through the roof

And to alleviate the problem temporarily we needed to bounce the instance and clear up the temp database files.

But luckily there is a patch.

More information provided in this article.

We also needed to disable the Read Committed Snapshot Isolation Level in order to keep the growth under control. More information about it this article.

Offending query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT HOST.NAME AS HOST, VOLUME.HOST_ID AS HOST_ENTITY_ID, CASE VOLUME_TYPE.TYPE
WHEN 'parallelScsi' THEN CAST(VOLUME_TYPE.HOST_ID AS VARCHAR(255)) + '-' + VOLUME.UUID
WHEN 'block' THEN CAST(VOLUME_TYPE.HOST_ID AS VARCHAR(255)) + '-' + VOLUME.UUID
ELSE VOLUME.UUID
END AS ENTITY_ID, VOLUME.CANONICAL_NAME, VPX_PSA_PATH.LUN_NUMBER, VPX_HOST_BUS_ADAPTER.DEVICE_VAL + ':C' + CAST(VPX_PSA_PATH.CHANNEL_NUMBER AS VARCHAR(3))+ ':T' + CAST(VPX_PSA_PATH.TARGET_NUMBER AS VARCHAR(3)) + ':L' +
CAST (VPX_PSA_PATH.LUN_NUMBER AS VARCHAR(3))
AS RUNTIME_NAME, VOLUME.DISPLAY_NAME AS NAME FROM VPX_SCSI_LUN VOLUME
INNER JOIN VPX_ENTITY HOST ON VOLUME.HOST_ID = HOST.ID
INNER JOIN VPX_PSA_DEVICE DEVICE ON VOLUME.KEY_VAL = DEVICE.LINK_KEY
INNER JOIN VPX_PSA_PATH ON VPX_PSA_PATH.SCSI_LUN_ID = DEVICE.ID
INNER JOIN (SELECT DISTINCT VPX_PSA_PATH.HOST_ID AS HOST_ID, SCSI_LUN.UUID AS UUID, CASE HOST_BUS_ADAPTER.VPX_TYPE
WHEN N'vim.host.ParallelScsiHba' THEN 'parallelScsi'
WHEN N'vim.host.FibreChannelHba' THEN 'fc'
WHEN N'vim.host.InternetScsiHba' THEN 'iscsi'
ELSE 'block'
END
AS TYPE FROM VPX_PSA_PATH, (SELECT VPX_SCSI_LUN.UUID AS UUID, VPX_PSA_DEVICE.KEY_VAL AS KEY_VAL FROM VPX_PSA_DEVICE, VPX_SCSI_LUN
WHERE VPX_PSA_DEVICE.LINK_KEY = VPX_SCSI_LUN.KEY_VAL) SCSI_LUN, (SELECT VPX_HOST_BUS_ADAPTER.VPX_TYPE AS VPX_TYPE, VPX_PSA_ADAPTER.KEY_VAL AS KEY_VAL, VPX_PSA_ADAPTER.HOST_ID AS HOST_ID
FROM VPX_PSA_ADAPTER, VPX_HOST_BUS_ADAPTER
WHERE VPX_PSA_ADAPTER.HOST_ID = VPX_HOST_BUS_ADAPTER.HOST_ID
AND VPX_PSA_ADAPTER.LINK_KEY = VPX_HOST_BUS_ADAPTER.KEY_VAL) HOST_BUS_ADAPTER
WHERE VPX_PSA_PATH.LUN_LINK_KEY = SCSI_LUN.KEY_VAL
AND VPX_PSA_PATH.ADAPTER_LINK_KEY = HOST_BUS_ADAPTER.KEY_VAL
AND VPX_PSA_PATH.HOST_ID = HOST_BUS_ADAPTER.HOST_ID) VOLUME_TYPE ON HOST.ID = VOLUME_TYPE.HOST_ID AND VOLUME.UUID = VOLUME_TYPE.UUID, VPX_HOST_BUS_ADAPTER, VPX_PSA_ADAPTER
WHERE (HOST.TYPE_ID = '1')
AND (VPX_PSA_PATH.ADAPTER_LINK_KEY = VPX_PSA_ADAPTER.KEY_VAL AND VPX_PSA_PATH.HOST_ID = VPX_PSA_ADAPTER.HOST_ID
AND VPX_PSA_ADAPTER.LINK_KEY = VPX_HOST_BUS_ADAPTER.KEY_VAL AND VPX_PSA_ADAPTER.HOST_ID = VPX_HOST_BUS_ADAPTER.HOST_ID)
AND VOLUME.LUN_TYPE='disk'
ORDER BY HOST_ENTITY_ID, ENTITY_ID, RUNTIME_NAME