MSSQL: is out of bounds for sequence "vpx_event_seq"

This can trigger in VCSA where database sitting in SQL Server. As the error message indicates, the sequence of 0 is outside of the bound of the sequence. Resetting should fix this issue.

setval: value -1786008095 is out of bounds for sequence "vpx_event_seq" (1..9223372036854775807)

VCDB=# SELECT setval('vpx_event_seq', 0)

VCDB-# ;

ERROR:  setval: value 0 is out of bounds for sequence "vpx_event_seq" (1..9223372036854775807)

VCDB=# SELECT setval('vpx_event_seq', 1000);

setval

--------

   1000

(1 row)

MSSQL: CHECKDB found 0 allocation errors and X consistency error

Found corruption error while running DBCC CHECKDB on the SQL Server DB.

Error messages

Msg 8990, Sev 16, State 1, Line 2 : CHECKDB found 0 allocation errors and 10 consistency errors in table 'VPX_EVENT' (object ID 138483572). [SQLSTATE 01000]

Msg 8989, Sev 16, State 1, Line 2 : CHECKDB found 0 allocation errors and 10 consistency errors in database 'VCDB'. [SQLSTATE 01000]

Msg 8958, Sev 16, State 1, Line 2 : repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (VCDB). [SQLSTATE 01000]

This is data loss corruption similar to Oracle’s datafile header corruption. Damage already been done. User can use repair_allow_data_loss feature as the SQL Server error suggested or restore from good backup. If backup available, I would prefer restoring the db or the specific table. The good news is, the VPX_EVENT isn’t a crucial table. It stores events related information.

Here is what can be done on the repair route. Another option is to clone the existing VCDB to a different MSSQL database and test out the procedures. If everything works smoothly (verified with no data loss), perform the same on the production side.

--If user decided to do it in existing db then take backup of the VCDB first

use VCDB

--verify the corruption still exist

DBCC CHECKDB

--this can only be done in single user mode. So, switch it to single user.

ALTER DATABASE VCDB SET SINGLE_USER;

--attempt with REPAIR REBUILD first where it will attempt to repair without data loss.

DBCC CHECKDB ('VCDB', REPAIR_REBUILD);

--To verify if corruption still exist.

DBCC CHECKDB;

--if it does, move on to repair allow data loss feature, this will result of masking out the corruption where there will unrepairable data loss.

DBCC CHECKDB ('VCDB', REPAIR_ALLOW_DATA_LOSS);

Once completed, set the db back to multi users, otherwise no one else can access the database.

ALTER DATABASE VCDB SET MULTI_USER;

DBA should find out what causes this corruption. This typically results of disk issues.

MSSQL: MSSQL on Linux datafiles with C prompt

Installing MSSQL on Linux is fairly straightforward.

Installation 

https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux

For MSSQL on Ubuntu simply follow this link.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu

SQL Server installation does enforce the 3.25Gig memory size. So, prior to start the installation makes sure your environment or VM do have around 4gig of memory to work with. I allocated 60Gig on my VMs. After the Ubuntu Server 64 bits deployment and MSSQL installation, I still have 52gig left.

The main point of this blog is not to guide the user to deploy MSSQL on Linux but to show one of the interesting behavior I noticed.

administrator@ubuntu:/var/opt$ sqlcmd -U SA

Password:

1> select @@version;

2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Microsoft SQL Server vNext (CTP1.1) - 14.0.100.187 (X64)

Dec 10 2016 02:51:11

Copyright (C) 2016 Microsoft Corporation. All rights reserved.

on Linux (Ubuntu 16.10)

(1 rows affected)

1> select name from sys.databases;

2> go

name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

(4 rows affected)

1>

As you can see here, the datafile path starts with C:\ prompt.

SELECT cast(DB_NAME(database_id) as varchar(20)) AS "Database Name",

       cast(Name as varchar(25)) AS "Logical Name",

       cast(Physical_Name as varchar (100)) as "Datafiles Path",

       (size * 8) / 1024 as "Size in MB",

       (size * 8) / 1024/ 1024 as "Size in Gig"

FROM sys.master_files;

Database Name        Logical Name              Datafiles Path                                                                                       Size in MB  Size in Gig

-------------------- ------------------------- ---------------------------------------------------------------------------------------------------- ----------- -----------

master               master                    C:\var\opt\mssql\data\master.mdf                                                                               4           0

master               mastlog                   C:\var\opt\mssql\data\mastlog.ldf                                                                              2           0

tempdb               tempdev                   C:\var\opt\mssql\data\tempdb.mdf                                                                               8           0

tempdb               templog                   C:\var\opt\mssql\data\templog.ldf                                                                              8           0

model                modeldev                  C:\var\opt\mssql\data\model.mdf                                                                                8           0

model                modellog                  C:\var\opt\mssql\data\modellog.ldf                                                                             8           0

msdb                 MSDBData                  C:\var\opt\mssql\data\MSDBData.mdf                                                                            13           0

msdb                 MSDBLog                   C:\var\opt\mssql\data\MSDBLog.ldf                                                                              0           0

A user can access the physical datafile with sudo.

administrator@ubuntu:/var/opt$ sudo ls -las /var/opt/mssql/data/

total 53320

    4 drwxr-xr-x 2 mssql mssql     4096 Dec 19 10:54 .

    4 drwxrwx--- 7 mssql mssql     4096 Dec 19 10:55 ..

 4096 -rw-r----- 1 mssql mssql  4194304 Dec 19 11:06 master.mdf

 2048 -rw-r----- 1 mssql mssql  2097152 Dec 19 12:06 mastlog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 modellog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 model.mdf

13632 -rw-r----- 1 mssql mssql 13959168 Dec 19 10:55 msdbdata.mdf

  768 -rw-r----- 1 mssql mssql   786432 Dec 19 10:55 msdblog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 10:55 tempdb.mdf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 templog.ldf

Interesting that SQL Server still keeping the Windows behavior.

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: ..... " is incorrectly formed. SQL Server cannot process this media family "

This means sql server backup and restore not in the same version. SQL Server backup is not backward compatible.

Trying to perform a backup from Windows 2008 R2 SQL Server 2012 and restore it to Windows 2008 R2 Windows 2008. Later, realized the version was not the same. Spun up a new VM with SQL Server 2012 and was able to restore it correctly.

Msg 3241, Level 16, State 13, Line 1

The media family on device 'C:\VCDB\vcdb1\vcdb1.bak' is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

MSSQL: Why does my sql server database growth and shrink on its own ?

Got a question from a user today that his SQL Server growing and shrinking on its own.Being shallow in SQL Server for the time being. That's triggered my curiosity and starting to do some reading about it.There are features in SQL Server database for this purpose.

Get a general idea of which DB actually growing or shrinking by monitoring it for a bit.

SELECT

    DB.name,

    MF.physical_name,

    MF.type_desc AS FileType,

    MF.size * 8 / 1024 AS FileSizeMB,

    fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB,

    mf.name LogicalName

FROM

    sys.master_files MF

    JOIN sys.databases DB ON DB.database_id = MF.database_id;

SELECT * FROM sys.database_files;

Look at the autoshrinking option

select name, is_auto_shrink_on  from  sys.databases;

From the GUI.

Also likelihood reindexing, large batch jobs and database shrinking. Those do cause transaction logs to grow and shrink.

Also might be DBA performing shrinkfile.

BACKUP LOG VCDB_LOG TO DISK = 'E:\SQLbackup.bak'

GO

DBCC SHRINKFILE (N'VCDB_LOG' , 10)

GO