Compress 10.2.1 database from 10.4.1 - Connection Failure

[Lynchburg_GISDivision]

Hey Everyone,

Hoping you can help in an error I'm receiving while trying to compress our enterprise database. The error is popping up in our development environment which we are currently using to test our upgrade to 10.4.1 from 10.2.2.

Current Desktop Environment: 10.4.1

Current Server Environment: 10.4.1

Current SDE Environment: 10.2.2

Current SQL Environment 2016 (running with 'Compatibility level' of SQL Server 2008)

We originally updated our desktop and server environments when our database was still in SQl Server 2008 R2. Once those migrations/upgrades were complete,  our DBA moved our enterprise database (development) to SQL Server 2016. This is when the error starting appearing. We're receiving this error message whenever we try to compress our database:

['Traceback (most recent call last):\n', '  File "\\\\tempest\\shared\\GIS\\System Administration\\Admin Tools Test\\Database\\Maintenance\\Hurley_Compress.py", line 66, in main\n    arcpy.Compress_management(hurley_sde)\n', '  File "C:\\Program Files (x86)\\ArcGIS\\Desktop10.4\\ArcPy\\arcpy\\management.py", line 4549, in Compress\n    raise e\n', 'ExecuteError: ERROR 999999: Error executing function.\nFailure to access the DBMS server [[Microsoft][SQL Server Native Client 11.0]Communication link failure] [dbo.DEFAULT][STATE_ID = 1493628]\nUnderlying DBMS error [dbo.DEFAULT]\nFailed to execute (Compress).\n\n']

We are able to perform all other standard and administrative tasks without issue such as reconciling/posting versions,  deleting and recreating versions, publishing feature services, editing, etc. Updating schema etc etc etc. This is the only time we see this error.

*** EDIT: We also tried upgrading the geodatabase to 10.4.1 without success.

Any help would be greatly appreciated.

[AlexanderBrown5]

Lynchburg,

Since SQL 2008 is not compatible with SDE 10.4, your DBA moved the database to SQL 2016 first?

SQL Server 2016 is not supported for SDE 10.2.2.  System Requirements 

1. Please put a support ticket in.  

2. This will allow for a potential solution in your current environment without having to entertain the following thoughts:

Your DBA should have done their due diligence to check compatibility, but since it already happened, you need to work a plan to remedy this compatibility situation.

Depending on the complexity of your SDE database, it might be easier to recreate your database as a fresh build in 10.4.1 (which does support SQL Server 2016) Microsoft SQL Server database requirements for ArcGIS 10.4.x—System Requirements | ArcGIS Desktop 

~Alex

[Lynchburg_GISDivision]

Appreciate your response regarding the due diligence of our DBA, although I question the need to phrase it that way. Especially coming from an Esri employee. I've dealt with Esri employees on many occasions, and I have to say this is the first time I've been dissapointed.

Our DBA migrated to 2016 SQL but set up the database to run in 'Compatibility level' of SQL Server 2008.  I neglected to mention that in my post. It has been modified to reflect the correct environment settings: 10.2.2 SDE in 2016 SQL running in Compatibility Level of SQL Server 2008. In addition, it's our development environment. Our DBA has multiple back ups of the database and has already, in an effort to trouble shoot this issue, restored backups from our production environment to the new 2016 SQL environment.

[VinceAngelo]

Best practice would be to use a commonly supported database (e.g. SQL Server 2012) for "release too far" migrations, then once upgraded (to 10.4.1), upgrade to the final RDBMS (2016).  Once the gulf between releases doesn't have any shared supported environments, you need to consider whether a "export to FGDB, import from FGDB" might be most appropriate (lest you need to iterate upgrades and platforms to reach the goal). 

In-situ upgrades give me agita, especially from a non-terminal release (10.2.1 vice 10.2.2) over intervening terminal releases (past 10.3.1), since the potential for upgrade failure increases when the test cases are fewer and farther between. Where possible, I prefer to review properties which can't be changed (more efficient spatial reference, data load in spatial index order,...), and evaluate whether an export/import upgrade is appropriate.

- V

[Asrujit_SenGupta]

Other than what has already been suggested above, it seems that you tried the compress using a Python script and provided that result.

Did you try Compressing directly in ArcCatalog? Does it throw the same error?

Also was the compatibility level altered after restoring the databases onto 2016 SQL Server instance?

ALTER DATABASE Compatibility Level (Transact-SQL) | Microsoft Docs 

What issues did you face while upgrading the gdb to 10.4.x?

[Lynchburg_GISDivision]

Appreciate everyone's responses and guidance. We worked with Esri Support (who were great as always  ) and they helped to identify our issue. Turns out there was a corrupt spatial index in our database that was preventing the database from being compressed.We ran Rebuild Spatial Indexes and Analyze Datasets as suggested and all issues were resolved.

Thanks again!