You are viewing limited content. For full access, please sign in.

Question

Question

Why Does the Forms 110_120 SQL Upgrade Script Set routing_xml Columns to NULL?

asked on September 24 Show version history

I have been working on upgrading our production Forms environment to version 12 tonight and it kept timing out in the Forms interface when trying to upgrade the database. I am now running the mssql_upg110_120.sql script through SSMS. It has reached the point in the script where it sets the UpgradeStep value to 1215. I noticed the next step in the script sets the routing_xml column values in the main_instance_extended_data and worker_instance_history_extended_data tables to NULL.

Why does it do that? We have over a million rows in each of those tables, which is why I believe it was timing out in the Forms interface.

1 0

Replies

replied on September 25 Show version history

Update: After timing out through the Forms interface twice, I have manually run the script in SSMS. It has been running the section of the script to set the routing_xml columns to null for 5hrs 46m. We have about 1,123,812 rows in the main_instance_extended_data table and 1,337,732 rows in the worker_instance_history_extended_data table. Why does it take so long?

The database size has also grown from around 271GB to now 514 GB. Why is it growing so much?

Also, is there a reason the SQL command doesn't exclude rows where the routing_xml column is already set to NULL like this?
 

UPDATE [dbo].[main_instance_extended_data]
SET routing_xml = NULL
WHERE routing_xml IS NOT NULL;

UPDATE [dbo].[worker_instance_history_extended_data]
SET routing_xml = NULL
WHERE routing_xml IS NOT NULL;

 

2 0
replied on September 25 Show version history

Update 2: It looks like the tempdb ran out of disk space. We are rolling back the update and will try again a different day.

2 0
replied on September 25

Next question... looking at those two tables in our database, they hold around 140 GB of data. Why is it being removed? Is it being copied somewhere else? Just trying to understand what's happening to all that data.

0 0
replied on September 25

Hi Blake, the routing_xml column represents cached xml data. Some of the old data was stored incorrectly, and the issue was fixed in the new version. Therefore, it's necessary to clear the old routing_xml during the upgrade process. And it will be regenerated automatically during usage.
As for the database size growth, it might be due to the database transaction log being enabled. If that's the case, you could try shrinking the log to reduce the size.

0 0
replied on September 26

Hi Zanbo. Our recovery model for the Forms database is set to Simple. We use a different utility for our SQL backups.

I have three questions regarding the update script:

  1. Can the UPDATE statements for the tables have a WHERE clause so that if the script needs to be run more than once it doesn't try updating rows where the routing_xml column is already NULL?
  2. Should the updating of the two tables be put into separate batches so there is less of a chance to fill up the tempdb?
  3. Can the individual update statements be put into a loop, so it only does x number of rows at a time, commits them, and then frees up the tempdb space again?
0 0
replied on September 27

Yes, absolutely. Thanks for clarifying the question. I have filed a bug #616658 to improve the upgrade script.
As a workaround, you can remove the section of setting routing_xml to null from mssql_upg110_120.sql and do it separately in the way you mentioned. There won't be any problem excluding rows where the routing_xml column is already NULL or applying batched updates.

2 0
replied on October 7

This is the script that I have put together. Do you see any problems with it?

IF EXISTS (
    SELECT *
    FROM [dbo].[cf_options]
    WHERE optionname = 'UpgradeStep'
      AND optionvalue IS NOT NULL
      AND ISNUMERIC(optionvalue) = 1
      AND CAST(optionvalue AS INT) = 1215
)
BEGIN
    DECLARE @BatchSize INT = 1000;
    DECLARE @RowCount INT;

    -- Update main_instance_extended_data in batches
    SET @RowCount = 1;
    WHILE @RowCount > 0
    BEGIN
        BEGIN TRANSACTION;

        WITH CTE AS (
            SELECT TOP (@BatchSize) *
            FROM [dbo].[main_instance_extended_data]
            WHERE routing_xml IS NOT NULL
        )
        UPDATE CTE
        SET routing_xml = NULL;

        SET @RowCount = @@ROWCOUNT;

        COMMIT;
    END

    -- Update worker_instance_history_extended_data in batches
    SET @RowCount = 1;
    WHILE @RowCount > 0
    BEGIN
        BEGIN TRANSACTION;

        WITH CTE AS (
            SELECT TOP (@BatchSize) *
            FROM [dbo].[worker_instance_history_extended_data]
            WHERE routing_xml IS NOT NULL
        )
        UPDATE CTE
        SET routing_xml = NULL;

        SET @RowCount = @@ROWCOUNT;

        COMMIT;
    END

    -- Update UpgradeStep
    IF EXISTS (
        SELECT *
        FROM [dbo].[cf_options]
        WHERE optionname = N'UpgradeStep'
    )
    BEGIN
        UPDATE [dbo].[cf_options]
        SET optionvalue = N'1216'
        WHERE optionname = N'UpgradeStep';
    END
    ELSE
    BEGIN
        INSERT INTO [dbo].[cf_options] ([optionname], [optionvalue])
        VALUES (N'UpgradeStep', N'1216');
    END
END
GO

 

1 0
replied two days ago

It looks well.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.