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)”

Postgres Corruption

Symptoms

Application logs showing

double register of key xxx
ERROR: invalid page in block x of relation pg_tblspc/x/x/x/x
ERROR: missing chunk number * for toast value * in pg_toast_*
checksum mismatch: disk has 0, should be xxx
DETAIL  could not open file “$psql/pg_clog/####”: No such file or directory
cp: cannot stat `pg_xlog/
Error “MultiXactId has not been created yet — apparent wraparound”

Continue reading “Postgres Corruption”

All things Database and VMware

Yvonne Murphy. Oracle Certified Master. VMware Certified Professional,  Member of the Office of the Chief Technical Ambassador VMware.

 

My role in VMware is to support customers running any Oracle product in a VM. But in all honestly Oracle do a great job of this so that doesn’t keep me busy.

There’s a lot of opportunities for a DBA to make herself useful in GSS though. I’m like a ninga. I go into those black boxes and I do some tricks in the database. Most of the blogs here are those scripts I use.

Do remember a bunny dies everytime you make a chance on one of these databases. so backup first!