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 through "Include execution trace messages"

https://docs.microsoft.com/en-us/sql/ssms/agent/write-execution-trace-messages-to-sql-server-agent-log-ssms?view=sql-server-2017

SQL Server Agent --- Right Click --- Properties

MSSQL: Datawarehouse: catalog.executable_statistics view missing

Trying to query the catalog.executable_statistics view and the object is missing. The reason is, there isn't SSIS project or packages being set up and deployed on this database. Quick viewing for SSISDB  (it can be any name aside from SSISDB) did not show up the Databases list. 

Msg 208, Level 16, State 1, Line 4
Invalid Object name 'catalog.executable_statistics'.

Integration Services Catalogs have no Projects or Packages.

Let's create Integration Services Catalog


Step 1: Create Catalog

There is a bug on my Create Catalog screen where the OK button is missing. Display Resolution issue.

Step 2: After creating the Catalog, right-click and Create Folder.

Step 3: Once Folder is created, Deploy the project.

Note: I have created a project to perform ETL prior to this exercise. So my package already existed.

The path can be located in the Visual Studio after the package is built.

Note: I have a little glitch here where the Next button is grayed out. Go into the Server Name and reselect and it will repopulate the Server Name and now the Next button is activated and the Path has a path as well. 

Completed the Project Deployment and package is created.

Step 4: Now execute the package.

The SSISDB should be created by now and the catalog.exectable_statistics should work.

That's it. Executable_Statistics view is great to provide information for a package execution time, duration and results.

References:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017

MSSQL: Database Diagram V17.9 feature crashed

Trying to map FKs between Dimension and Fact Tables and Database Diagram crashed with error message "Index was outside the bounds of the array". Can't get around it and error sounds like running out of memory. 

Closed everything and reopened get me going again. The issue seems pretty random.

MSSQL: Create a copy of a table and database

Create a copy of a static database.

CREATE DATABASE VCDB_COPY AS COPY OF VCDB

In a way, this is very much creating and restoring from a full backup. 

MSSQL: Instant File Initialization

Enabling Instant File Initialization can potentially improve over performance. It works a similar manner as thick eager provisioning in vCenter than letting it grows as it needs by zeroing the disk.

How To Enable Instant File Initialization
1. Open Local Security Policy and go to Local Policies –> User Rights Assignment.

2. Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.
3. Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.

MSSQL: Using DBCC to gather statistic

Trying to get statistic for performance diagnostic for a bloated vCenter table - VPX_EVENT.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-2017

dbcc show_statistics ("table_name", index_name);

use vcdb_2016

dbcc show_statistics ("vpx_event", pk_vpx_event);

dbcc show_statistics ("vpx_event", pk_vpx_event) with histogram;

MSSQL: Rebuild Index ONLINE

Comparing Rebuild Online on databases that I am familiar with. 

SQL Server
alter index xxxx rebuild ON VPX_VM REBUILD WITH (ONLINE = ON);
alter index xxxx rebuild ON VPX_VM REORGANIZE;

Oracle
alter index xxxx on VPX_VM online;
alter index xxxx rebuild ON VPX_VM REORGANIZE;

Postgresql
alter index xxx on VPX_VM online;
(No Reorganize feature for Postgresql until Postgresql 10)
Alternatively, performing Rebuild Index concurrently.

The awkward way of SQL Server to rebuild index online.