reduce size of VC database

backup and shutdown VC first

For SQL server

SELECT
t . NAME AS TableName ,
s . Name AS SchemaName ,
p . rows AS RowCounts ,
SUM( a . total_pages ) * 8 AS TotalSpaceKB ,
SUM( a . used_pages ) * 8 AS UsedSpaceKB ,
(SUM ( a . total_pages ) – SUM ( a . used_pages )) * 8 AS UnusedSpaceKB
FROM
sys. tables t
INNER JOIN
sys. indexes i ON t . OBJECT_ID = i . object_id
INNER JOIN
sys. partitions p ON i . object_id = p . OBJECT_ID AND i . index_id = p . index_id
INNER JOIN
sys. allocation_units a ON p . partition_id = a . container_id
LEFT OUTER JOIN
sys. schemas s ON t . schema_id = s . schema_id
WHERE
t . NAME NOT LIKE ‘dt%’
AND t . is_ms_shipped = 0
AND i . OBJECT_ID > 255
GROUP BY
t . Name , s . Name , p . Rows
ORDER BY TotalSpaceKB DESC;

exec sp_spaceused

What’s taking up the majority of the data in vpx_field_val

SELECT count(SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1)) , SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1)
from vpx_field_val
group by SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1)
order by SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1) desc;

its’ suspected that the majority of this data is tasks if so remove not running tasks

If they’re tasks – the script below shows the ones which aren’t valid.

delete
from vpx_field_val
where mo_id like ‘task%’
and SUBSTRING(mo_id, CHARINDEX(‘-‘, mo_id) + 1, LEN(mo_id)) not in (
select task_id
from vpx_task
where complete_state not in (‘success’, ‘error’)
or complete_time >= (getdate() -1) );

if they’re vm’s

select field_id, mo_id
from vpx_field_val
where mo_id like ‘vm%’
and SUBSTRING(mo_id, CHARINDEX(‘-‘, mo_id) + 1, LEN(mo_id)) not in (
select id
from vpx_vm
);

delete
from vpx_field_val
where mo_id like ‘vm%’
and SUBSTRING(mo_id, CHARINDEX(‘-‘, mo_id) + 1, LEN(mo_id)) not in (
select id
from vpx_vm
);

SELECT * into VPX_INT_ARRAY_BK FROM VPX_INT_ARRAY
WHERE TYPE_ID = 42 AND EXISTS(SELECT 1 FROM VPX_VM_FLE_SNAP_DISK_UNIT WHERE ARRAY_ID=VPX_INT_ARRAY.ARRAY_ID);

truncate table VPX_INT_ARRAY;

INSERT INTO VPX_INT_ARRAY ([ARRAY_ID] ,[TYPE_ID] ,[VALUE] ,[ARRAY_INDEX] ,[MO_TYPE] ,[MO_ID])
SELECT [ARRAY_ID] ,[TYPE_ID] ,[VALUE] ,[ARRAY_INDEX] ,[MO_TYPE] ,[MO_ID] FROM [VPX_INT_ARRAY_BK];

DROP TABLE VPX_INT_ARRAY_BK;

SELECT * into VPX_TEXT_ARRAY_BK FROM VPX_TEXT_ARRAY
WHERE  EXISTS(SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID);

truncate table VPX_TEXT_ARRAY;

SET IDENTITY_INSERT VPX_TEXT_ARRAY ON;

insert into VPX_TEXT_ARRAY (ARRAY_ID,[TYPE_ID],VALUE,MO_TYPE,MO_ID,SURR_KEY)
select ARRAY_ID,[TYPE_ID],VALUE,MO_TYPE,MO_ID,SURR_KEY from  VPX_TEXT_ARRAY_BK;

SET IDENTITY_INSERT VPX_TEXT_ARRAY OFF  ;

drop table VPX_TEXT_ARRAY_BK;

truncate table vpx_property_bulletin;

EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s