SQL Server Database maintenance plan consideration

[AlbertWT]

Hi All,

Before performing the vSphere 5.5 Upgrade, I just wanted to know what are the recommended steps for the Windows VCenter SQL Server maintenance ?

Because from my screenshot below, I cannot find any job to perform any of these:

Update statistics of the tables and indexes on a regular basis for better overall performance of the

database.

„ As part of the regular database maintenance activity, check the fragmentation of the index objects and

recreate indexes if needed (i.e., if fragmentation is more than about 30%).

as recommended by http://www.vmware.com/pdf/Perf_Best_Practices_vSphere5.5.pdf document.

Because when I created and scheduled the SQL DB maintenance plan using the builtin SQL DB maintenance wizard, I got this error instead:

Executing the query "ALTER INDEX [IX_History] ON [monitor].[HistoryTrig..." failed with the following error: "The index "IX_History" on table "HistoryTriggAlarm" cannot be reorganized because page level locking is disabled.".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any help would be greatly appreciated.

Thanks.

[AlbertWT]

Additional Note:

When I run DBCC SHOWCONTIG with ALL_INDEXES it returns the result as in the attached file.

The VCenter Server VM is also running Microsoft SQL Server 2008 Standard Edition (64-bit) (SP2) - 10.0.4000.0 .

[AlbertWT]

Does VMware provides some SQL maintenance plan htat can be scheduled regularly ? or can I use the scheduled SQL DB maintenance plan wizard ?

[Styvboard]

We use Ola Hallengrens script for all our SQL Servers, works really good. Script and  good documentation here -> https://ola.hallengren.com/

Maint plans in SQL has been messy since long, should be a bit better in SQL 2014 but I'll stick to Olas scripts.

[AlbertWT]

Styvboard‌ how did you impelement that SQL maintenance job from Ola ?

The builtin SQL Server 2012 maintenance plan always give up in the below error:

Failed:(-1073548784)

Executing the query "ALTER INDEX [IX_History] ON [monitor].[HistoryTrig..." failed with the following error: "The index "IX_History" on table "HistoryTriggAlarm" cannot be reorganized because page level locking is disabled.".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Does using Ola script will still be supported by VMware ?

[JinuV]

please refer the Vmware Link

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=201213...

[admin]

You can introduce any maintenance routine you find suitable for your environment.

There are 2 guidelines to be followed:

1. Don't change the db schema structure, e.g. drop or add a column in a table such as vpx_version or any other.

2. Don't change the vCenter Server data, e.g. update/delete/insert/etc. data from any table such as vpx_vm.

Also before running the upgrade you need to look at the pre-requisites: Prepare Microsoft SQL Server Database Before Upgrading to vCenter Server 6.0

[Styvboard]

Hallengren script shouldn't be a problem with VMWare.

Before running script, edit in notepad and correct the backup directory to your preference.

The script creates stored procedures in master database and 10 jobs without schedules. Edit jobs and add a Schedule, Daily for backups and weekly for cleanup of logs and dbcc. Run dbcc jobs Before backups. And SQL backups Before traditional file backup.

What's with the autocorrection that does random capital letters on some Words....??? 

[Styvboard]

Not sure what's wrong with the Index job. I'd try to run Hallengrens index job if you installed it. Examples on the bottom of https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Some intelligence in index scripts, doing reindex or rebuild depending on the status of the index.