Saturday, October 29, 2016

MSSQL: Script to reorg vCenter indexes in SQL Server

Script to reorg vcenter indexes above 70% fragmentations.

SQLCMD -i d:\reorf.sql -o d:\reorg.txt

use vcdb
select 'alter index ' +  ind.name + ' on ' + OBJECT_NAME(ind.OBJECT_ID) + ' reorganize;' from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind  ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
 WHERE indexstats.avg_fragmentation_in_percent > 70  ORDER BY indexstats.avg_fragmentation_in_percent DESC;


Changed database context to 'VCDB'.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter index VPXI_EVENT_EVENT_TYPE on VPX_EVENT reorganize;
alter index VPX_EVENT_ARG_F1 on VPX_EVENT_ARG reorganize;
alter index PK_VPX_DBM_COUNTER_VALUE on VPX_DBM_COUNTER_VALUE reorganize;
alter index VPXI_EVENT_CATEGORY on VPX_EVENT reorganize;
alter index VPXI_EVENT_CREATE_TIME on VPX_EVENT reorganize;
alter index VPXI_EVENT_EXTENDED_CLASS on VPX_EVENT reorganize;
alter index VPX_EVENT_ARG_F2 on VPX_EVENT_ARG reorganize;
alter index PK_VPX_SAMPLE_TIME1 on VPX_SAMPLE_TIME1 reorganize;
alter index PK_VPX_SAMPLE_TIME2 on VPX_SAMPLE_TIME2 reorganize;
alter index PK_VPX_SAMPLE_TIME3 on VPX_SAMPLE_TIME3 reorganize;
alter index VPX_SAMPLE_TIME3_U1 on VPX_SAMPLE_TIME3 reorganize;
alter index PK_VPX_TABLE on VPX_TABLE reorganize;

(12 rows affected)




MSSQL: Transferring ownership from one owner to another in sql server.

Occasionally, this could happen in vCenter where some objects belong to other owners aside from dbo.

Referemce: https://msdn.microsoft.com/en-us/library/ms173423.aspx


Transfer vpx_version from dbo to guest.
alter schema guest transfer dbo.vpx_version;
sp_tables vpx_version












select distinct sys.schemas.name from sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id and sys.schemas.name <>'sys';










Transfer vpx_version from guest to dbo.
alter schema dbo transfer guest.vpx_version;
sp_tables vpx_version



To change all objects from none dbo to dbo schema.

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o. Name
    FROM sys.Objects o
    INNER JOIN sys .Schemas s on o. schema_id = s. schema_id
    WHERE s.Name = 'dbo'
    And (o. Type = 'U' Or o .Type = 'P' Or o.Type = 'V')

MSSQL: Getting more detail information for failed jobs.

Often when a job failed, it provides trivial information for diagnostic. In order to expand the detail of the failed job, it can be done th...