Geodatabase compression problem

Question

I have been trying to compress a geodatabase on a SQL 2008 r2 utilising SDE 10.0 and I'm getting this message.

Failed to compress the database.

Underlying DBMS error[Microsoft SQL Server Client 11.0: Transaction(process ID 122) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.][dbo.DEFAULT]

deleted-user-eL7_ccoObXzi · Answer

if you go into arc catalog and right click on the database connection then select administration. in there open the locks tab and sort to see if you have any exclusive state locks. We have had problems with that in the past not allowing a reconcile it may be causing you trouble. Also we run our compressions as the SDE user not dbo. we have a script the cleans up the users before a compression. these are not all the steps we run but you get the idea of the order of operations.

* disallow connections
* Kill all direct connections
* Shutdown the service
* update statistics
* compress
* reindex all tables
* update usage statistics
* start the service
* allow connections

Hope this helps...

FalconESRIKW · Answer

Disconnecting all users and then running compress may work. However the compress should work even when users are connected to the geodatabase. (partial compress)

It would be worth trying/checking following:

1. Try compress using ArcSDE command - 'sdeversion -o compress' (using version 10 dlls) or try compress from Desktop 10.

2. If you do not have latest service pack for 10 already applied (SP 5) on both SDE and Desktop, apply the same and try the compress from 10.1 if it works OK otherwise compress again using any of the methods in 1 above.

Was it the first time that you were running compress from 10.1 desktop? If answer is yes, above method may help us to determine if compress from 10.1 is causing the deadlock resulting in failure of compress.

Good read about deadlocks:

Detecting and Ending Deadlocks

Thank you!

BrianBorg1 · Answer

Thanks for your feedback Asrujit

Yes there are users editing this data. In the past we used to carry out compression with users connected and always completed compression successfully. Nevertheless, I'll take your suggestion and try it with all users disconnected.

We are using ArcCatalog 10.1 to run the compression on SQL server 2008 r2 with ARCSDE 10.0