C135 PCS 7 (PCS7)
6C
6 Cielo Ciencia y Tecnología
·Publicada en C135 PCS 7 (PCS7)

Resolving Process Historian 2014 SP2 Update 2 Database Schema Upgrade Failures (Return Code 1)

Resolving Process Historian 2014 SP2 Update 2 Database Schema Upgrade Failures (Return Code 1)

In SIMATIC Process Historian (PH) 2014, when upgrading from SP2 to SP2 Update 2 results in the automated database maintenance wizard failing during script execution 'Update_To_14022.bat with return code 1' and halting with 'Database update failed', the error is caused by SQL Server table locking, insufficient transaction log space in tempdb, or an inactive SQL Server Agent service. The patch script attempts to alter partitioned schema tables while background connections or maintenance jobs hold exclusive table locks. To resolve this error, engineers must review 'UpdateDatabaseErrors.log', ensure the SQL Server Agent service is running, terminate orphaned database connections, and execute the failed schema update script manually using sqlcmd under administrative privileges.

Table of Contents

Process Historian Database Migration Architecture (Version 14020 to 14022)

SIMATIC Process Historian 2014 operates on a dedicated Microsoft SQL Server instance (HISTORIAN). Long-term process telemetry, alarm journals, and batch records are stored within dynamically partitioned SQL tables (HistorianStorage, HistorianCommon).

When installing cumulative software updates (such as PH 2014 SP2 Update 2):
- The installer deploys patched service binaries and replaces database scripts in C:\Program Files\Siemens\ProcessHistorian\Setup\Database\.
- Upon system reboot, the Process Historian Database Update Wizard launches automatically.
- The wizard executes incremental Transact-SQL migration batch scripts sequentially:
- Update_To_14021.bat: Upgrades schema from version 14020 to 14021.
- Update_To_14022.bat: Upgrades schema from version 14021 to 14022.
- Each script must return exit code 0. If any SQL statement encounters a deadlock, schema constraint violation, or memory allocation failure, the batch script aborts with return code 1, leaving the database in an inconsistent maintenance state.

Root Causes of Update_To_14022.bat Script Failures

Diagnostic analysis of return code 1 reveals three primary failure modes:

  1. Active Database Connections and Table Locks:
    - The script Update_To_14022.bat applies structural modifications (e.g., adding indices, altering column widths) to high-volume time-series partition tables.
    - If local monitoring tools, SQL Server Management Studio (SSMS), or lingering background services (HistorianStorage, HistorianReporting) hold active connections, the schema alteration encounters a lock timeout.
  2. SQL Server Agent Service Stopped:
    - Several migration routines register scheduled partition compression tasks with SQL Server Agent (HISTORIAN). If this service is disabled or stopped, the batch script fails when calling agent stored procedures.
  3. Transaction Log or TempDB Exhaustion:
    - Migrating multi-gigabyte historical databases requires extensive sorting in tempdb. If the hard drive hosting tempdb lacks sufficient free disk space (minimum 20 GB recommended), the SQL transaction aborts with error 9002 (The transaction log is full).

Analyzing UpdateDatabaseErrors.log and SQL Exception Codes

The exact Transact-SQL error is recorded in the installation log directory:
C:\ProgramData\Siemens\Logs\UpdateDatabaseErrors.log
(or UpdateDatabase.log).

Common SQL exception signatures:
- Msg 1205, Level 13 (Deadlock Victim): Two update transactions competed for partition locks.
- Msg 5069, Level 16 (ALTER TABLE statement failed): Table locked by an active SPID.
- Msg 1101 / 1105 (Could not allocate space in database): Disk volume full during index rebuild.

Step-by-Step Procedure: Repairing and Finalizing Database Updates

Follow this procedure to resolve the script failure and complete the database upgrade:

Step 1: Prepare the SQL Server Environment

  1. Open Windows Services (services.msc).
  2. Verify that SQL Server (HISTORIAN) is running.
  3. Verify that SQL Server Agent (HISTORIAN) is started and set to Automatic startup.
  4. Stop all Process Historian application services: CCCAPHServer, HistorianStorage, and HistorianReporting.

Step 2: Terminate Active Connections

  1. Launch SQL Server Management Studio (SSMS) as Administrator and connect to .\HISTORIAN.
  2. Open a New Query window and execute the following command to place the database in single-user mode:
    sql USE master; ALTER DATABASE HistorianStorage SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE HistorianStorage SET MULTI_USER;
  3. Close SSMS to release all query window handles.

Step 3: Execute the Failed Migration Script Manually

  1. Open an Administrative Command Prompt (cmd.exe).
  2. Navigate to the database script directory:
    cd "C:\Program Files\Siemens\ProcessHistorian\Setup\Database\"
  3. Execute the failed script manually to observe detailed console feedback:
    Update_To_14022.bat
  4. The batch script connects via sqlcmd, executes the schema migration, updates the version table to 14022, and exits with return code 0.

Step 4: Restart the Process Historian Services

  1. Once the script finishes cleanly, reboot the Process Historian server.
  2. Open the Process Historian Management Console; confirm that the database version displays 14022 and the operating state indicates 'Active'.

Diagnostic Matrix for Process Historian Database Migration Scripts

The table below outlines common migration script aborts:

Script NameTarget VersionCommon ErrorRoot Cause and RemediationUpdate_To_14021.bat14021Return code 1Missing SQL Server Agent service; start service and re-runUpdate_To_14022.bat14022Return code 1Table lock on HistorianStorage; set single-user and re-runUpdate_To_14023.bat14023Disk fulltempdb or log drive exhausted; expand disk spaceDatabaseMaintenance.batGeneralAccess DeniedCommand prompt not elevated; run as Administrator

Verification

To confirm that the Process Historian database update is complete:
1. Database Version Audit: In SQL Server Management Studio, execute:
SELECT CurrentVersion FROM HistorianCommon.dbo.VersionTable;
Confirm that the returned version integer is 14022.
2. Management Console Status: Open PH Management Console; verify that server state displays 'Active' with steady green indicator.
3. Data Ingestion Check: Confirm that telemetry packets received from WinCC OS servers are actively being committed into SQL table partitions without error logs.

Limitations and Operational Precautions

Engineering teams must observe the following precautions:
- Always create a complete SQL database backup (HistorianStorage and HistorianCommon) before initiating any Process Historian service pack or cumulative update installation.
- Never terminate a running SQL database migration script via Task Manager; interrupting schema modification mid-transaction can corrupt database metadata.
- Always validate final license quantities and MLFBs against the applicable Siemens catalog and authorized distributor quotation when ordering Process Historian packages.

Engineer FAQ

Where can I find the master installation logs for Process Historian?

Process Historian logs all setup and database migration transactions under C:\ProgramData\Siemens\Logs\ and C:\ProgramData\Siemens\ProcessHistorian\LogFiles\.

Can I roll back to SP1 if the SP2 Update 2 database update fails?

Rollback requires uninstalling the update and restoring the full SQL Server database backup taken prior to starting the SP2 upgrade; Transact-SQL schema changes cannot be uninstalled automatically.

Does Process Historian SP2 Update 2 require updating WinCC PH-Ready?

Yes. When applying Update 2 to the Process Historian server, Siemens strongly recommends updating the PH-Ready component on all connected OS servers to the matching update level.

Technical References

Related Articles

Related Tags

  • Process Historian
  • PH 2014 SP2
  • Database Update
  • SQL Server
  • Update_To_14022.bat
6 respuestas