Advice on Export (and Obliterate) please

[JohnVel]

I am trying to export branches, primarily to give me some idea of how much of my database each one is using. However, although I can export simple branches, the first two big branches that I've tried have failed.

We are using Vault 3.5 with 10 users; we have a 6GB database with 10 repositories (the main repository has 32,000 versions, 540,000 files and 17,800 folders); we have
Windows Server 2003 running SQL Server 2000.

The database seems ok for normal use (but performanace is occasionally slow, which is why I'm looking at database size).

I am running the export tool directly on the server. Each export failed after an hour. The two errors are:

A database error has occured (FailDBReader)

13/11/2006 14:31:46 <generic>: [<No Name>:4912] Folder Export: Error in ProcessResults_GetFolderExportInfo()An exception was encountered retrieving the Folder Transaction information for $/Impact Language/Language [VaultServiceAPILib.VaultSoapException: 1104 : FailDBReader
at VaultService.VaultAdminService.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, String[][]& arBranchObjIDsTxIDs)] [System.Web.Services.Protocols.SoapException: VaultServiceAPILib.VaultSoapException: 1104 : FailDBReader
at VaultService.VaultAdminService.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, String[][]& arBranchObjIDsTxIDs)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at VaultClientNetLib.AdminService.VaultAdminService.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, String[][]& arBranchObjIDsTxIDs)
at VaultClientNetLib.VaultConnection.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, HybridDictionary& hdBranchObjIDsTxIDs)
at VaultClientOperationsLib.ClientInstance.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderID, Boolean bGetDeleted, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, HybridDictionary& hdBranchObjIDsTxIDs)
at VaultFolderExportImport.ExportWorkerThread.GetFolderTxInfo(GetFolderTxInfoParam ti)]VaultServiceAPILib.VaultSoapException: 1104 : FailDBReader
at VaultService.VaultAdminService.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, String[][]& arBranchObjIDsTxIDs)
[System.ApplicationException: An exception was encountered retrieving the Folder Transaction information for $/Impact Language/Language ---> System.Web.Services.Protocols.SoapException: VaultServiceAPILib.VaultSoapException: 1104 : FailDBReader
at VaultService.VaultAdminService.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, String[][]& arBranchObjIDsTxIDs)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at VaultClientNetLib.AdminService.VaultAdminService.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, String[][]& arBranchObjIDsTxIDs)
at VaultClientNetLib.VaultConnection.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderObjID, Boolean bGetDeletedItems, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, HybridDictionary& hdBranchObjIDsTxIDs)
at VaultClientOperationsLib.ClientInstance.GetInfoForFolderExport(Int32 nRepID, Int64 nTxID, Int64 nFolderID, Boolean bGetDeleted, Int64[]& arObjIDs, VaultFolderExportTxInfo[]& arTxIDs, HybridDictionary& hdBranchObjIDsTxIDs)
at VaultFolderExportImport.ExportWorkerThread.GetFolderTxInfo(GetFolderTxInfoParam ti)
--- End of inner exception stack trace ---]An exception was encountered retrieving the Folder Transaction information for $/Impact Language/Language


The tree structure could not be built.

13/11/2006 16:09:53 <generic>: [<No Name>:4912] Folder Export: Error in ProcessResults_ProcessTransaction() An exception was encountered retrieving the Tx Information for $/Impact Source/Impact_3.1. Tx: 94619 Previous Tx: 94493 [VaultServiceAPILib.VaultSoapException: 1108 : FailDBInvalidTreeRecords
at VaultService.VaultAdminService.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nBaseRevision, Int64 nTargetRevision, VaultRepositoryDelta& rd)] [System.Web.Services.Protocols.SoapException: VaultServiceAPILib.VaultSoapException: 1108 : FailDBInvalidTreeRecords
at VaultService.VaultAdminService.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nBaseRevision, Int64 nTargetRevision, VaultRepositoryDelta& rd)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at VaultClientNetLib.AdminService.VaultAdminService.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nBaseRevision, Int64 nTargetRevision, VaultRepositoryDelta& rd)
at VaultClientNetLib.VaultConnection.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nSrcRevision, Int64 nDestRevision, VaultRepositoryDelta& rd)
at VaultClientOperationsLib.ClientInstance.GetRepositoryDeltaForFolderExport(Int32 repid, Int64 srcrevision, Int64 destrevision, VaultRepositoryDelta& rd)
at VaultFolderExportImport.ExportWorkerThread.ProcessTransaction(String strProcessMsgType, Int64 nTxID, Int64 nPrevTxID)]VaultServiceAPILib.VaultSoapException: 1108 : FailDBInvalidTreeRecords
at VaultService.VaultAdminService.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nBaseRevision, Int64 nTargetRevision, VaultRepositoryDelta& rd)
[System.ApplicationException: An exception was encountered retrieving the Tx Information for $/Impact Source/Impact_3.1. Tx: 94619 Previous Tx: 94493 ---> System.Web.Services.Protocols.SoapException: VaultServiceAPILib.VaultSoapException: 1108 : FailDBInvalidTreeRecords
at VaultService.VaultAdminService.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nBaseRevision, Int64 nTargetRevision, VaultRepositoryDelta& rd)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at VaultClientNetLib.AdminService.VaultAdminService.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nBaseRevision, Int64 nTargetRevision, VaultRepositoryDelta& rd)
at VaultClientNetLib.VaultConnection.GetRepositoryDeltaForFolderExport(Int32 nRepID, Int64 nSrcRevision, Int64 nDestRevision, VaultRepositoryDelta& rd)
at VaultClientOperationsLib.ClientInstance.GetRepositoryDeltaForFolderExport(Int32 repid, Int64 srcrevision, Int64 destrevision, VaultRepositoryDelta& rd)
at VaultFolderExportImport.ExportWorkerThread.ProcessTransaction(String strProcessMsgType, Int64 nTxID, Int64 nPrevTxID)
--- End of inner exception stack trace ---]An exception was encountered retrieving the Tx Information for $/Impact Source/Impact_3.1. Tx: 94619 Previous Tx: 94493


I have also been struggling to obliterate branches. These operations usually fail after 2 to 3 hours, with an unspecific error (The Vault server could not be contacted to perform the operation. Your network connection to the server may have been interrupted. Please verify your network settings using the Options dialog under the Tools menu in the Vault GUI Client. The underlying connection was closed: An unexpected error occurred on a receive.). I'm gathering information on this problem and I'll probably be posting about this soon.


My questions are:
- can you offer any advice/solution to these specific export errors?
- is it possible that my database is corrupt and are there any utilities to check this? (I've already used CHECKDB)
- is exporting/obliterating branches a reasonable way to improve performance, or should I be pursuing other solutions? (upgrade to SQL Server 2005?)
- should I wait for Vault 4.0 (I've read that you're expecting to improve obliterates) and when is this likely to be released?
- how come I'm directed to non-existent help pages? namely:
http://kb.sourcegear.com/VaultHelp/erro ... ?code=1104
http://kb.sourcegear.com/VaultHelp/erro ... ?code=1108

Thanks.

[JohnVel]

I forgot this question:

- is there a better way of analysing branch sizes than by exporting them? (I only need an approximate idea of relative sizes)

[lbauer]

We can deal with the export/obliterate errors, but first we should address the overlying issue -- you're exporting to improve Vault performance.

Unless the Vault Server machine has inadequate resources (CPU, memory, drive space), a 6 GB database is not particularly large and may not be responsible for performance problems. What kinds of performance problems are you experiencing? How many users do you have?

It's possible there are some database inconsistencies causing the export to fail, but it also looks like there was failed database connection, which could have caused problems with the export.

You could try increasing the SQL command timeout in the Vault.config file, located in the VaultService directory. Look for <SqlCommandTimeout>360</SqlCommandTimeout>. Increase the number to something larger, like 720 or even 3600. Then try the export again.

[JohnVel]

Performance:
We usually have 8 to 10 users connected throughout the day. The main source folder has 11,000 files in 400 sub-folders. It can take up to an hour to do a fresh Get on this folder. This time has slowly been creeping up as our database size has increased.

Export:
I've increased the timeout as you suggested. I've tried one export and got the "tree structure could not be built" error again. I've also managed to get myself another server to play with. I've copied the database across and tried the export there - same thing. I noticed the error is on the same transaction (Retrieved transaction 73 of 11360, after an hour). So I guess there is something amiss with the data.

It would be useful to be able to gauge the data size used for a single branch, because we have one folder containing three fairly large binaries which are updated on a daily basis. If necessary (and if possible) we could export the history for safekeeping, delete/obliterate the files and re-add them.

[lbauer]

is there a better way of analysing branch sizes than by exporting them?

You can select the folder (branch) in the folder tree, right-click and select Properties. This will give you information on the folder, including tree size and size on disk.

It can take up to an hour to do a fresh Get on this folder. This time has slowly been creeping up as our database size has increased.

Do you run regular maintenance on your Vault database:
http://support.sourcegear.com/viewtopic.php?t=2924

Is there enough disk space on the SQL Server machine? Be sure to put the database in Simple Recovery mode so the sgvault_log.LDF file doesn't get too big.

Is there a lot of fragmentation on the drive where the database resides? This can cause poor performance.

Other tips to optimize Vault:

http://support.sourcegear.com/viewtopic.php?t=4206

[JohnVel]

Here's where I'm up to now.

I'm convinced that the export is a totally separate problem. Whatever bad data is causing it, it's not affecting our day-to-day work, so I'm going to ignore it for the moment.

I have been able to obliterate everything I've wanted to on the copy of my database, so this issue isn't due to bad data. On my live database I've upped the SqlCommandTimeout value (to 5000) and there is no noticeable difference.

To see if a smaller database actually does improve performance, I'm going to wait until the Xmas break, re-copy the database onto my test server, redo the obliterates, then copy it back. If it makes no difference to the performance then I don't need to worry about obliterates ever again.

Longer term, I'm still planning on a beefier server, SQL Server 2005 (and who knows, maybe Vault 4).

To answer your latest suggestions/questions:

You can select the folder (branch) in the folder tree, right-click and select Properties. This will give you information on the folder, including tree size and size on disk.

No, I meant the size of the entire history of the folder. I know this isn't a straightforward calculation, because of how you store file differences. I was after an indication of how big the exported folder might be (or rather, how smaller the database would be without it). Just curious.

Do you run regular maintenance on your Vault database

Yup, I'm doing this every couple of days.

Is there enough disk space on the SQL Server machine? Be sure to put the database in Simple Recovery mode so the sgvault_log.LDF file doesn't get too big.

There's plenty of room on the D drive, which is where the data is. I am using Simple Recovery. The log file is currently 200MB.

Is there a lot of fragmentation on the drive where the database resides? This can cause poor performance.

I've defragged all drives. The data files have no fragments.

Other tips to optimize Vault...

Yes, I think it's time for me to study this again.

Just one thing you haven't addressed - version 4. Can I read about this somewhere (other than the post on Eric's weblog, which is over a year old)? Or is it all very hush hush?

[JohnVel]

Ah, I've just spotted the v4 info tucked away here: http://www.ericsink.com/entries/SourceG ... tress.html
I'll need to curb my enthusiasm for a while longer.