10

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?

  • Is your site running 2010? If so, was it at some point upgraded from 2007? If so read this: blogs.msdn.com/b/sowmyancs/archive/2012/06/29/… – Shane Wealti Feb 20 '14 at 13:28
  • Also: There is a memory leak with the AllDocStreams table in SP2010 SP1 before the August 2012 Cumulative update. Even if you delete the items and files, it keeps the BLOB data in this table. – Shane Wealti Feb 20 '14 at 13:31
  • Interesting. This database was indeed migrated from 2007. I'm certainly going to try the steps in that blog post. When you create an answer for this, I'll reward the bounty when it checks out. – Jasper Feb 21 '14 at 6:48
14

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.

  • 1
    You are right, auditing is turned on and I hadn't considered that. I'll await the DBA's results and award you with the bounty when your hunch was correct :) – Jasper Feb 19 '14 at 14:43
  • 1
    I had the exact problem with 5 content db each 100GB, auditting was set to ON, but trimming was OFF, application has been running for 8 months, audits were 60% of the real data. I reduced it to 15 day, and saved 180GB. – Luis Valencia Feb 19 '14 at 15:19
  • Was the content stored in the AuditData table only, or are there also report documents filling up the space? Note the addition I made to my post for more detail. – Jasper Feb 20 '14 at 8:26
  • I just read your update regarding AllDocStreams and AllDocVersions - auditing would not contribute to their size. The large discrepancy though, between what's reported in CA (or script) vs. what's actually in those tables looks related to a separate known issue (since resolved) which could occur when deleting webs from script (see my edited answer above). – NYCHarry Feb 20 '14 at 17:26
  • Hmmm ok. I got another possible option which has to do with migration (see above). I'm going to try that first before contacting support, since that'll probably take more time. – Jasper Feb 21 '14 at 6:49
8
+150

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.

  • Your answer helped me find the correct cause, which was the 2007 --> 2010 migration. Thanks. – Jasper Feb 24 '14 at 14:49
5

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.

  • Hi Arko. We're running third party DocAve tooling, so I used that to check for orphaned sites; didn't come up with anything. Do you know how to check for deleted BLOB data in a content database? Or better yet: how to clean it? I checked the sites recyclebin, that isn't showing a lot of data in the first of second stage, most definitely not the amount of overhead we're seeing here. – Jasper Jan 28 '14 at 15:29
  • Also, what does SQL Server Mgmt Studio tell you about unused space in the Database (rightclick DB node, click properties and then look at 'space available')? The file itself might be eligible for a large 'Shrink' operation. Note that this could potentially take a LONG time on a DB of that size. – tyshock Jan 28 '14 at 19:59
  • As already stated; the database doesn't contain air / shrinking does not help. – Jasper Jan 29 '14 at 13:11
5

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... ;)")

  • Was already answered in comment to another answer (i'll update the question); recycle bin data isn't the culprit. – Jasper Feb 19 '14 at 14:42
4

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.

3

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.

  • Recycle bin isn't the culprit, already stated. Versioning is counted in the quota stats, so that should have been visible. – Jasper Feb 19 '14 at 14:43
3

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
3

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.