Vault database size - very large, then suddenly 10x smaller

M Wickardt · Post by **M Wickardt** » Mon Jan 15, 2007 4:48 am

Hi sourcegear support,

I'm worried about the sudden changes in the size of my sourcegear database.

On Oct 9 2006, the size of the backups was around 110 MB.

In the end of november, we added around 20 MB of sourcecode files (around 5000 of them) to a separate repository, in a couple of days.

The daily backups we made of the vault database, increased in size by a huge amount... from around 200 MB to around 2.5 GB. I've been meaning to investigate that, but it was no immediate problem.

This week, the size of the backup dropped suddenly from 2.408.621.568 bytes (woensdag 10 januari 2007, 23:01:55) to 269.207.040 bytes (donderdag 11 januari 2007, 23:00:16). We did nothing special at the time, no big checkins.

We never obliterate. We keep all our history.

Can you explain this behaviour to me? All seems in order, but... strange. I could send you the backup files to investigate.

The logfile didn't mention anything special.

The database itself is still around 2.5 GB, but one Shrink action should take care of that. I'd first like to be sure that there are no problems.

versions:
Vault client is version 3.5.0.4741
Vault server is version 3.5.0.4741

Kind regards, Tijs Wickardt, Bertus Distributie

lbauer · Post by **lbauer** » Mon Jan 15, 2007 8:49 am

The sgvault_log.ldf can grow very large if you have many transactions and are using Full Recovery mode for SQL Server. Backing up the database and truncating the log would cause the log to shrink, since the transaction log is no longer needed once you do a full backup.

Also, SQL Server can generate checkpoints which truncate unused portions of the log.

More info here:
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
http://msdn2.microsoft.com/en-us/library/ms189085.aspx

M Wickardt · Post by **M Wickardt** » Mon Jan 15, 2007 9:04 am

Thank you for the info, but I'm not sure if it answers my question.

I was talking about the size of the *backup* itself, and to my knowledge, the log is not included in the backup if you don't specify it.

We make a backup the following way (after logging in in a script)

BACKUP DATABASE sgvault TO DISK = c:\backup\vault.sqlbackup WITH INIT

lbauer · Post by **lbauer** » Mon Jan 15, 2007 9:36 am

It's possible the database was reduced in size due to a cleanup of checkoutlists.

Everytime someone does a checkout, a checkoutlist is created. Each checkoutlist has a list of all the items checked out.

A Checkoutlist stays in the database for 5 days (default), after which it is automatically deleted. You can have thousands of checkoutlists.

It's possible to have millions of checkoutlist items if a user did a very large checkout and/or if the checkoutlists are not being cleaned up for some reason. I just dealt with one customer who had more than 9 million rows in the database for checkoutlistitems.

So when the checkoutlists/checkoutlistitems get cleaned up, it could cause the size change you're seeing.

BTW: we are changing checkoutlists in Vault 3.5.2, so they will no longer add exponentially to the database. This release should be available this spring.

M Wickardt · Post by **M Wickardt** » Tue Jan 16, 2007 6:38 am

Thanks Linda, that seems to have been the case.

In the backup before the big shrink in size, there were about 3.7 million records of tblcheckoutlistitems. Right now we have about 1700.

Strange though that the cleanup took a lot more than 5 days to start - the last 'biggish' (500 files) checkout/checkin was on Dec 20, 2006; the last big (5000 files) checkout/checkin was on Nov 29, 2006.

Good to hear you're adressing this potentially hazardous space hog behaviour in a future release.

Kind regards, Tijs Wickardt, Bertus Distributie