Proper Methodology to Archive

[vtpsu99]

Hi,

I have a question regarding Archiving. We would like to free up space on our database which we backup to each night, and we would like to be able to archive and purge old projects. We can use that Export tool to archive, but I understand that if we use obliterate then that tool can no longer be used. If we use the delete after the export then, the history is still there thus taking up the same amount of space.

How can we archive and purge periodically such that if absolutely necessary we can restore from the archive but in practice we are keeping a leaner repository that will minimize used space and backup space needed?

thanks,

James

[Beth]

The first thing to consider is the reason behind archiving for your organization.

Can you tell me:

• 1) Do you experience any slowness in transactions?
  2) Do you have tight space limits on the server?
  3) Does your server size increase at a very fast rate?
  4) Is it possible this is a left-over habit from VSS? Since SQL Server doesn't have the probably that the VSS databases did, it's not necessary to treat a SQL database in the same manner.

[vtpsu99]

Hi Beth,

To answer your questions:

1) No significant slowness.
2) No we do not have tight space limits on the server
3) Yes our server size increases at a fast rate
4) No this is not left over habit from VSS.

There are definitely projects that if archived would help overall management of the repository and because we have seen issues with the server running out of space (which we have responded with adding more space), the archiving would be helpful.

thanks,

James

[Beth]

Can you tell me the current size of your sgvault.mdf on disk and what kind of rate it's increasing at?

What will probably help you in this situation is to create more repositories. The issue with Obliterate and Export/Import is on a per repository basis. Obliterating in one repository does not affect another repository.

Do you have a lot of items shared or reused between projects? If not, then by creating more repositories and either keeping either just one project or a small select number of projects that might be archived around the same time, then you will be able to easily accomplish what you want. For example, if you have a repository with 3 projects that will be archived within a month of each other, what you would do is Export each project as you are finished with it, then delete the project. You would not use obliterate in this case. Once you are finished with that repository and have what you need exported, then you would just delete the repository. Deleting a repository removes it from the database entirely without affecting other repositories. In my example, you could also just wait until all 3 projects are done, export the entire repository at once, and then delete the repository.

If you think there's a chance you might need to revisit a project, you might consider a second Vault server just to restore old projects to and just have only admin have access to that Vault server. That way you'd have an opportunity to test the import and an admin could easily pull data from the archived projects.

[vtpsu99]

Hi Beth,

The current size of the sgvault.mdf is 18 GB and the log file is 19 GB and the increase rate is about 10MB a day but that isn't the major concern.

Does the size of the file and the log file size seem reasonable? The way that projects typically work here is that they are part of the main repository and eventually certain patches or releases will be branched off and retired. So for awhile, they are sharing resources but then they are put into a retired folder that can be archived. Is there any best practice we should be using for archiving projects and maintaining a reasonable size mdf and log file?

If we had multiple repositories then we could move retired projects to say a retired repository. Would we export a folder before we "retire" it and then import it into the retired repository? Also, regarding the separate vault server, with licensing, are we allowed to install vault on a separate server for backups?

Thanks, and I appreciate your help with this.

[Beth]

The transaction log being larger than the database file can cause some problems depending on what version of SQL Server you are using. What version of SQL Server are you on?

There isn't really a best practice for what you are currently doing. Your database size isn't unreasonable. If you experience any performance issues, then you might check out our KB article on Vault performance: http://support.sourcegear.com/viewtopic.php?t=4206.

To maintain the size of your database, you will want to review the SQL database maintenance article: http://support.sourcegear.com/viewtopic.php?t=2924. Using the Simple recovery model will make sure that SQL keeps the log files small. SQL is supposed to maintain those during backups, but some versions of SQL don't always do that.

Creating another repository on the same Vault installation as a place to put a copy of the entire project doesn't gain you anything. To keep it in the same database, it would be better to just use the delete function and not obliterate it. Then it's there in the same database in case you need to bring it back to look at it, but it keeps your Vault tree that you see smaller and more responsive.

What I meant with multiple repositories is that any project that doesn't have links to other projects, you would give it its own repository.

For backup purposes, the second server should be fine.

I suspect I may be misunderstanding part of your scenario. If you'd like I can take a closer look. Just send an email to support at sourcegear.com (attn: Beth) with a link to this forum thread.