Content database size vs site collection size

Question

We have one rather large content database in our environment. In this content database, a single site collection resides. In Central Admin, this site collection lists as being 464699 MB big. Since this is is the only site in the database, I'd expect the database to be roughly equal in size. It is not however, actually the SQL MDF file is about 800GB's. What could be causing the extra overhead and how do I limit it? I checked with our DBA's, there is no air / shrinking won't help.

I know we're not exactly following best practices and we shouldn't go over 200GB's, but that's a different story.

Update: i also ran Get-SPSiteAdministration to check the DiskUsed property of the site, that returned 487317920421.

Update: the recycle bin data as mentioned in some answers is not the culprit. The second stage bin is nearly empty and first stage is also not taking up much space.

Update: our DBA ran the report to show the table sizes. The result is pretty weird. The AllDocStreams table is 535004448 KB (= 522465MB) big. The AllDocVersions table: 270222MB. Central Admin is still giving me 479219MB for the entire site collection, which is less than both numbers individually. Audit table is only 15418MB, so that's not the major culprit.

Conclusion: most space is used by documents, not even the olders versions. And that space alone is more than what Central Admin is telling me. As already stated, the recyclebins are pretty much empty so those cannot be the issue either. So now the question is: is the number in Central Admin just wrongly calculated, or is there some left over / orphaned data in those tables which is not calculated but still there for some reason? And if that would be the case: how can I get rid of it?

Answer 1

Given the amount of bloat and the fact there's not much in the 2nd stage recycle bin, it's more than likely Auditing has been turned on for the site collection. I believe trimming of the audit log is not enabled by default either, which could cause your content db to grow out of hand pretty quickly depending on level of activity within the site collection.

You can verify if you can have one of the SQL DBAs to run a standard 'disk usage by tables' report on the content db. The audit log is stored in the AuditData table - if that table is at the top of your report, you have your culprit.

Update: There was a known issue, resolved in the August 2012 CU, where deleting an SPWeb via script would cause leaked streams in the AllDocStreams table as well as orphaned documents (and related versions). There are a number of situations you might see the behavior including SharePoint migration and/or upgrade depending on the method.

Even though it was resolved in the August CU, any orphaned data created prior to the release would still need to be manually removed (which is the only resolution as far as I'm aware). The resolution is supported by Microsoft, but only by contacting them first through their support channels so they can verify that's the underlying issue.

Answer 2

To find the table(s) which are taking up the most space run this query in SQL Server Management Studio (with your content database selected to query against). That should help you narrow it down to a particular table or tables and then from there you can determine what can be done to reduce the size. You might want to throw some "with nolock"s in there to prevent locking issues.

Alternatively for a less detailed view use Reports > Standard Reports > Disk Usage By Table from SQL Server Management Studio.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Based on the update you provided it sounds like the AllDocStreams table is the main culprit. I didn't see a version mentioned but if you are running SharePoint 2010 you might want to look into an issue with SP 2010 - SP1 pre August 2012 Cumulative update. Even if you delete the items and files, it keeps the BLOB data in this table.

If your database has been upgraded from 2007 to 2010 it might also be affected by an issue with unused space not being reclaimed. Microsoft has a blog post that discusses the issue and how to recover from it.

Answer 3

This can mean any of the two things.

1. A previously deleted site collection is still residing in the content database if your farm is a SP2010 SP1 and above. This can account to different size of the site collection that shows up in the central admin and a different size for content database keeping all deleted site collections.

2. Your Content Database contains decent size of BLOB data which is either in deleted state from the site or a large amount of second stage recycle bin items are occupying the physical space.

Try to validate these. In any case, you should use a SQL based backup,restore approach from here on considering the current size of your content database.

Answer 4

When a site collection's size is calculated, the size of the second stage recycle bin is not included. However, the size of 2nd stage recycle bin will contribute to the size of your content database.

Perhaps your 2nd stage recycle bin is large? Consider emptying the recycle bin and/or adjusting its quota settings in Central Admin (web application settings > general).

References:

1. http://social.technet.microsoft.com/Forums/sharepoint/en-US/15892efe-5575-4c66-b5bb-3211ef1a913e/need-powershell-script-to-get-website-storage-details?forum=sharepointgeneralprevious

2. http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=270 (see comment #2 entitled "Cool, but leaves out 2nd stage Recycle Bin... ;)")

Answer 5

You may have deleted (but not removed) SPSites in your content DB, which shows in SSMS, but not in Central Administration. To check if you do, run:

>Get-SPDeletedSite

If there are deleted sites, you'll see these sites listed like this:

WebApplicationId   : 009c1289-392b-43a6-8222-146117074738
DatabaseId         : 88efc46f-5a2b-4171-81cb-7577da65bac3
SiteSubscriptionId : 00000000-0000-0000-0000-000000000000
SiteId             : 86d69f7e-3a9e-4f65-8eba-550d77f93a18
Path               : /sites/collaboration
Scheme             : Http
Url                : http://portal/sites/collaboration
DeletionTime       : 2014-02-21 11:14:18

WebApplicationId   : c73bb367-d329-4ef2-aef7-323ce207d2a9
DatabaseId         : aeb2d6ab-9f9b-493f-aefc-797d4b591f43
SiteSubscriptionId : 00000000-0000-0000-0000-000000000000
SiteId             : 9d0ed313-0353-4cec-817a-a491a8c5fc26
Path               : /
Scheme             : Http
Url                : http://portal/
DeletionTime       : 2014-02-19 15:27:37

Copy the SiteId and run

>Remove-SPDeletedSite –Identity 9d0ed313-0353-4cec-817a-a491a8c5fc26

When you're done, check if the content DB is using less space, or if you are allowed to shrink it more than before.

Answer 6

Jasper,

There are few things that counts for the site usage. - Check recycle bin (end user as well as site collection recycle bin). - Check document library version, this is the major culprit in using up the space, the size of document gets multiplied by the number of version. Try to limit version to last 10. - Check for database fragmentation, this also causes the database to grow large.

Hope this gives you some pointers.

Answer 7

There could be much empty space in your database file as SharePoint databases are not shrink automatically. When you delete anything in database the space of it still remains in database file and is used in the future. You can free most of this space manually by shrinking the database file by these commands.

You should also check tables in SharePoint content database. You can use SQL management studio and either SQL commands (see above) or rightclick on the table - Properties - Storage.

My candidates for large amount of data tables are:

• AuditData - SP server only - contains info about your users accessing information (items, documents, pages) - can be extremely large! - not counted in site collection size
• AllDocs - stores documents and all versions
• AllUserData - contains data from lists and documents' metadata - not document it itself
• NameValuePair_yourlanguage - contains data of all your indexed columns - not counted in site collection size

Answer 8

so couple of things to check. From the SQL reports look a like actual data presents in the table rather free space and moreover no AuditTabel issue.

I would recommentwo things. run the below powershell and see if it update the storage.

$URL = "https://<spsite>/<sitecollection>"
$Site = Get-SPSite -identity $URL
$Site.RecalculateStorageUsed()
$site.Usage.Storage

see if it update the Storage.

2nd thing, open the site in the SharePoint designer, from site information page see what is the total size.

3rd thing, try to run SQL Backup of the Content DB and see if it reduce the size.

Answer 9

just like to note this issue has been answered on technet forum that asks exactly your question!

There is no real direct relation between the DB size and the content size, there is but not in a direct fashion. The size of the DB is usually the size when the content was at its peak. There is no automatic shrinking of the content DB's in SharePoint. You can configure it from SQL but it is not always a good thing to do. To have a Content database size that is larger than the content is usually a good thing because is gives SHarePoint room to grow without the performance hit of having to auto grow the DB at every write/save. If the database size relation to the content size gets unreasonably big then a shrink will be motiovated, but do a proper research on the effects first.

In your case, if the difference between the actual content size and the DB size is up to 1GB to 333GB, then there has to be something else. Has a backup ever been made for instance? That will commit all transactions to the DB and the size will be a lot smaller.

The mdf file size can be smaller than the DB size in Management studio, that is because Management studio shows the 'current database' including uncommited transactions in the transaction log(ldf).

I hope this helps

Regards

Use powershell:

determine the size of a site collection using powershell

Get-SPSiteAdministration | ft url,diskused -AutoSize 

http://technet.microsoft.com/en-us/library/ff607824.aspx