AllDocVersions & AllDocStreams table size increased after upgrading to SharePoint 2010
Recently I was working on an upgrade project, MOSS 2007 to SharePoint Server 2010 and faced an interesting issue. We had several databases with sizes spans from 50 GB – 200 GB in size. After the upgrade we have noticed that all of the upgraded databases in SharePoint Server 2010 were almost 40% – 60% larger than before the upgrade. This was a major concern for my customer as it was making the total storage running out of free space.
As we all know, in SharePoint Server 2010 , there is new schema change in AllDocStreams & AllDocVersions tables. All document versions were stored in AllDocVersions table with its binary stream (Content Column) in MOSS 2007. In 2010 , it will be in AllDocStreams table, so while performing an upgrade, upgrade action will move all of file version contents from the AllDocVersions table to the AllDocStreams and then it will drop the “Content” column from the AllDocVersions Table.
Please look at this article for getting more information : https://technet.microsoft.com/en-us/library/cc262891.aspx#section1
Here is a diagrammatic representation of it.
You can see more details of what are the things happened during the upgrade by looking at the upgrade log file created for each upgrade session.
UpgradeAllDocStreamsAndAllDocVersions is the upgrade action which takes care of this process.
powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:16 PM]: Begin Initialize()
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:16 PM]: End Initialize()
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [INFO] [6/15/2012 4:20:16 PM]: Upgrade AllDocStreams.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:16 PM]: Begin Upgrade()
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:16 PM]: Adding new AllDocs columns
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:16 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:19 PM]: Updating AllDocs.Version columns
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:19 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:19 PM]: Adding AllDocs.Version computed column
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:19 PM]: Dropping column AllDocs.Version
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:19 PM]: Executing SQL DDL Script.
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:19 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:19 PM]: Dropping table ADVUpgrade.
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:19 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:19 PM]: Renaming Version column to UIVersion in AllDocVersions
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:19 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:19 PM]: Adding Version column to AllDocVersions
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:19 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:19 PM]: Populating temporary table
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:19 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Populating AllDocVersions Version column
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Dropping default constraint on AllDocVersions.UIVersion
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Dropping index AllDocVersions.AllDocVersions_RbsId
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Dropping table ADVUpgrade.
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Dropping index AllDocStreams.AllDocStreams_CI
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Dropping index AllDocStreams.AllDocStreams_RbsId
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Adding Version column to AllDocStreams
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:20 PM]: Populating AllDocStreams Version column
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:20 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:23 PM]: Dropping default constraint on AllDocStreams.InternalVersion
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:23 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:23 PM]: Dropping default constraint on AllDocStreams.Level
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:23 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:23 PM]: Dropping column AllDocStreams.Level
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:23 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:20:23 PM]: Populating AllDocStreams Content Column
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:20:23 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: Dropping column AllDocVersions.Content
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:26:04 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: Unregistering RBSID
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:26:04 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: Dropping column AllDocVersions.RbsId
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:26:04 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: End Upgrade()
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:26:04 PM]: Calling set SchemaVersion on Database GWIM_NonCustom_FIRSTTest_ContentDB, Status = Upgrading.
[powershell] [SPContentDatabaseSequence] [DEBUG] [6/15/2012 4:26:04 PM]: Executing SQL DDL Script.
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: Begin Commit()
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: End Commit()
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: Begin Dispose()
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: End Dispose()
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: SQL Query Count=27
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: SPRequest Objects=0
[powershell] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [6/15/2012 4:26:04 PM]: Execution Time=348196.711919752
After the upgrade even though the “content” column was dropped from the AllDocVersions table size was not changing which was causing the growth in overall size of the upgraded databases.
Below is an e.g: of table differences in 2007 and 2010. Here we can see that after the upgrade AllDocStreams size increased with the total size of AllDocVersions table, 34+7 = 41 GB, but we can see that the size of the AllDocVersions table remain unchanged in 2010.
Tables size details before the upgrade ( while it was MOSS 2007 Database)
Table Name |
# Records |
Reserved (KB) |
Data (KB) |
Indexes (KB) |
Unused (KB) |
dbo.AllDocStreams |
76,268 |
34,167,400 |
34,140,080 |
15,168 |
12,152 |
dbo.AllDocVersions |
16,658 |
7,040,072 |
7,000,888 |
40 |
39,144 |
Tables size details after the upgrade ( after becoming a SPS 2010 Database)
Table Name |
# Records |
Reserved (KB) |
Data (KB) |
Indexes (KB) |
Unused (KB) |
dbo.AllDocStreams |
88,713 |
41,130,848 |
41,115,096 |
4,016 |
11,736 |
dbo.AllDocVersions |
16,658 |
7,040,072 |
7,000,888 |
40 |
39,144 |
This was the whole reason for getting large databases after the upgrade. Below is the root cause of the above behavior and resolution.
After dropping a variable-length column in SQL Server, additional steps are occasionally needed to reclaim the space that was once allocated by that column. Steps are given below.
- Backup the 2010 database.
- Execute the below SQL statement against your content database and make note of the Data and Unused values. sp_spaceused 'AllDocVersions'
- In order to free the space within the AllDocVersions table, execute the below SQL statement where Content_DB is the name of your content database.
DBCC CLEANTABLE (Content_DB,AllDocVersions) - Execute the below SQL statement against your content database and make note of the Data and Unused values. The above command should have freed the space previously allocated by the Content column. This will be evident by the Data size being reduced and the Unused size being increased.
sp_spaceused 'AllDocVersions' - In order to return this freed space to the operating system, execute the below SQL statement where Content_DB is the name of your content database.
DBCC SHRINKDATABASE (Content_DB) - It is always good to check fragmentation percentage after performing the operations like step 3. Please follow this article to get to know about it: https://technet.microsoft.com/en-us/library/cc262731.aspx#DBMaintenanceForSPS2010_MeasureFragmentation
After all, don’t forget to follow the database maintenance tasks for SharePoint 2010 , please refer it here : https://technet.microsoft.com/en-us/library/cc262731.aspx
Comments
Anonymous
March 17, 2013
Is this supported by Microsoft to perform ??Anonymous
March 17, 2013
yes, but contact Microsoft Support to confirm you are facing the same issue or not before you do it yourself. Also you have to do all these testing in a test environment first not in production.Anonymous
March 25, 2013
Did not work. I moved the database to our dev instance and ran the DBCC Cleantable command on the database/table but there is no free space to shrink and the table size is the same. The table is about 12 GB now.Anonymous
March 25, 2013
Verify that you are in the exact scenario as mentioned in this article. If you need assistance on this can contact MS support.Anonymous
February 24, 2015
This was exactly the issue that we were facing and couldn't find a solution for a longtime. This article really helped us finally resolve the issue. Thanks,Anonymous
February 24, 2015
The weird thing is it didn't happen or was not noticable with other databases which were much smaller than the one (close to 100 GB) we were having issue with. This leads me to believe that the size did play a role during the upgrade. SharePoint recommends not to have large Content Databases for a reason :)