Does "database size" for repository stats = physical size?

[anotherlab]

Does the database size list for the repository stats web page correspond to the actual physical size of the database files? The size of our sgvault.mdf is much larger than the sum of database size values listed for each repository (as listed on the statistics page for the repository). Our database size appears to be increasing by about 10% each month and that doesn't appear to match our development efforts.

Is there any way to query the database to see if there any large files that would balloon up the repository size? I want to rule out the possibility that someone may be versioning large binary files.

[lbauer]

The database size listed includes file lengths, but does not include the Labels table. Could labels be adding to the size? You can delete un-needed labels to reduce the size of the database. There are other overhead items that contribute to the size of the database that are not included in the repository size.

You might also check your database Properties->Files to see if the Autogrow percentage in increasing the database size more than it needs to.

[anotherlab]

If I add up the values listed as "Database Size:" on the statistics page for each repository, I get roughly 250MB. The size of the sgvault.mdf is about 1.6GB. I don't think the difference comes from the labels.

Is there any way to query the database, either by SQL or by the API, to find the largest files? We have 12 repositories, used by different teams, and it's possible that some may have put binary files in the past and I would like to find files that are not supposed be under version control. Could there be deleted files that could be increasing the size of the database?

[lbauer]

As I mentioned earlier, there are more than files in the database. There's a certain amount of "overhead" with file history, checkout tables, labels, user info, etc. In addition there can be space that has been allocated by SQL Server but not used.

The SQL Server database allocates a chunk of disk space for the .mdf and
the .ldf when it needs room. When it gets to the point that the "free
space" within those files is used up, SQL Server allocates the next
chunk of disk space based on a % or flat MB rate. That's why I suggested you check the
growth rate in your database properties. Also, shrinking the database can release
the unused space.

Meanwhile, here's a query to determine your 100 largest files in the database.

Code: Select all

select top 100 v.objid, i.filelength, n.name from 
sgvault.dbo.tblfsobjectversionfileinfos i
inner join sgvault.dbo.tblfsobjectversions v ON v.objverid = i.objverid
inner join sgvault.dbo.tblfsobjectversionnames n ON v.vernameid = 
n.vernameid
group by v.objid, n.name, i.filelength
order by i.filelength desc

[anotherlab]

Thanks Linda,

That query identified a frightening number of non-source code files that some has added to one of the repository. How can I modify that query to get the repository name and path ($/this/that/) for each row? That would make cleaning up the repositories much simpler.

[jeremy_sg]

I would recommend starting from the NodeCounter power toy, which you can download from:

http://www.sourcegear.com/vault/vaultpowertoys.html

It will tell you which folders currently in your repository have the largest total file size. You can quickly browse through your repository to find the problematic areas.

Secondly, I'm still not convinced that labels are not an issue. Please email support at sourcegear.com ATTN: jeremy with a link to this thread, and I'll send you a SQL query to show you the size of your database tables.