Skip to Content
Author's profile photo Bret Halford

Upgrade Tip – Run DBCC GAM(dbid,0,0,’check’) after upgrade to 15.x

Recommendation: After upgrading a pre-15.0 server or database to 15.x, run DBCC GAM(dbid,0,0,’check’) as a postupgrade step against each upgraded database.

Why:  To minimize the server downtime for the upgrade, some upgrade steps are deferred to a later time.  Prior to 15.0, extent records on allocation pages contained the slice id for partitioned tables.  The upgrade to 15.0 unpartitions all tables, but the old slice information is left in the extent records.  The extent records with old slice information are subsequently corrected by whatever process first acesses the allocation page after upgrade.

In general, this deferred processing just causes a slight one-time slowing of performance as the extent records are corrected.  However, DBCC CHECKSTORAGE in particular experiences two types of problems the first time it is run after the upgrade (when accessing extent records with the old slice information).  The first is greatly decreased performance, the second is spurious 100016 faults (page not used error). On very large databases, the number of faults reported can be overwhelming.

Sample output with spurious 100016 faults:

1> dbcc checkstorage(testdb)

2> go

Checking testdb: Logical pagesize is 2048 bytes

DBCC CHECKSTORAGE for database ‘testdb’ sequence 7 completed at April 18 2013

2:33PM. 0 faults and 1777 suspect conditions were located. 0 checks were

aborted. You should investigate the recorded faults, and plan a course of action

that will correct them.

Suspect conditions are to be treated as faults if the same suspect condition

persists in subsequent CHECKSTORAGE operations, or if they are also detected by

other DBCC functions.

1> sp_dbcc_faultreport

2> go

Database Name : testdb

Table Name               Index          PartitionId

         Fault Type

         Page Number

———————— ————– ———————————-

         ——————————————————–

         ———————————-

test                        0                 32000114

         100025 (row count error)

                      NULL

test                      255                 32000114

         100016 (page not used error)

                       491

test                      255                 32000114

         100016 (page not used error)

                       881

test                      255                 32000114

         100016 (page not used error)

                       887

[snip]

In both cases, the problem is not seen on subsequent executions of CHECKSTORAGE on the same database.  One case with a 120 GB database reported CHECKSTORAGE taking 26 times longer for the first execution than subsequent executions.  CHECKSTORAGE, for reasons as yet unknown, is very inefficient at upgrading the extent records, while DBCC GAM(dbid,0,0,’check’) has proven to be the fastest method for upgrading all the extent records.  So I recommend running DBCC GAM after the upgrade, before running DBCC CHECKSTORAGE.  It isn’t critical that you do so though, as the problem does clear itself up over time or by the first running of CHECKSTORAGE.

This issue is being tracked under change requests (CR) 681056 (spurious faults) and 632800 (slowness)

About DBCC GAM:

 

The GAM (Global Allocation Map) is an ASE datastructure used to quickly identify which allocation units have at least one free extent on them.  It is essentially a large bitmap with one bit for each allocation page.  The bit is set on when all 32 extent records are allocated and cleared when less than 32 extent records are allocated.  The ‘check’ option of DBCC GAM reads each allocation page in the database to verify that the corresponding GAM bit is set correctly.   Prior to ASE 15.7 ESD 4, the ‘check’ option incorrectly outputs a message saying that the database must be in single user mode.  This message should be ignored as the check is performed regardless.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      I know it doesn't currently, but shouldn't the upgrade process run dbcc gam automatically behind the scenes?  IMO, it should run dbccs on the system tables prior to modifying them and again after it upgrades them. 

      Author's profile photo Bret Halford
      Bret Halford
      Blog Post Author

      There is a tradeoff between doing these checks (and perhaps even more, such as a full checkdb) and completing the upgrade quickly.  There is a large (and vocal) percentage of the user base that has a strong need to minimize downtime during the upgrade, which is why the work of updating the allocation pages is deferred.

      I agree that it would be prudent to run checks on the system tables before and after upgrade.  But I think they will remain optional manual steps as long as there is a need to minimize downtime.

      DBCC GAM is really only being recommended here as a workaround to the comparatively abysmal performance of checkstorage in performing the same deferred updates to the allocation pages.  There is an open CR for the performance issue in checkstorage, if it is resolved the recommendation  to run dbcc gam as a workaround will go away.

      Author's profile photo Former Member
      Former Member

      Running dbcc's on the system tables should be a mandatory operation for the upgrade.  System tables in even large environments rarely take more than a few minutes.  Perhaps it should be enabled by default with the option of "bugger off, I know what I'm doing." 

      The risk of a corrupted system table outweighs the downtime issue imo.  User tables should be left up to the dba/management.