Geodatabase compression problem

[BrianBorg1]

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]

[TimWitt2]

Brian,

I moved your post, from ArcGIS Online to Managing Data. This should give you a higher chance of getting your question answered.

Tim

[AsrujitSengupta]

Is there anyone else connected to that geodatabase\editing while you are performing the compress?

Can you disconnect all the users if possible and then try the compress once?

Also please provide the version of Desktop being used, SQL Server database version and sde geodatabase version.

[BrianBorg1]

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

[FalconESRIKW]

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!

[deleted-user-eL7_ccoObXzi]

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.

1. disallow connections
2. Kill all direct connections
3. Shutdown the service
4. update statistics
5. compress
6. reindex all tables
7. update usage statistics
8. start the service
9. allow connections

Hope this helps...