Resolve SharePoint Errors caused by SQL Express

Today I tested my bulk import tool as part of a data migration. The code in the custom developed tool is straight-forward and must move list items and documents from over 600 SharePoint 2007 sites into a single SharePoint 2010 site.

Soon after starting my tests I ran into SharePoint foundation problems.

Problem:

Whenever the code attempts to upload a file into SharePoint I got the following error:

The URL 'Document_2011/0000000zzz/Test Document.docx' is invalid.

It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web. Troubleshoot issues with Microsoft SharePoint Foundation.

I then tried to manually delete a file from the SharePoint site and received the following error:

The server has encountered the following error(s):

ExampleFile.pdf

Exception from HRESULT: 0x80131904

When I try to delete the entire document library I receive the following error:

Exception from HRESULT: 0x80131904

Troubleshoot issues with Microsoft SharePoint Foundation.

Investigation:

I investigated the SharePoint content database.

In Central Admin, go to Application Management and then to Manage Content Databases.

You can select the web application and view the details of the associated content database.

I then logged into SQL Server Management Studio and confirmed that the content database ran out of space. As you can see from the screenshot below, the database size is 4138.19 MB and there is only 0.13 MB free space. (ensure to log into the correct instance of SQL in order to see the content databases)

Cause:

When SharePoint 2010 is installed the user can select to perform a ‘Basic’ or default installation.

Selecting a ‘Basic’ installation is a big mistake, but unfortunately guys who are new to SharePoint might not be aware of the pitfalls.

When you installed SharePoint 2007 as basic, the installation used the Windows Internal Database, a version of SQL Express with no size limit, but in SharePoint 2010 the installation will be done on SQL Express which has a 4GB size limit.

SQL Express 2008 R2 has a 10GB size limit, but it might still not be enough.

This was exactly my problem –SharePoint was installed as a ‘Basic’ installation on a test environment and I was trying to upload a large number of files – around 5.3GB in total size. Once I reached the limit of 4GB SharePoint could not perform any operations on the database which requires more space.

Remember that even if you try to delete items from SharePoint, they are only moved to the recycle-bin so the used database space does not become available immediately.

Solution:

There are two ways to solve this:

1-You can either upgrade from SQL Express to SQL Server,

or

2- Implement EBS (External Blob Store - move the BLOB items out of the database onto a file system. For more information on this please see http://msdn.microsoft.com/en-us/library/bb802812.aspx )

I decided to go with option #1-Upgrade my SQL Instance from SQL Express to SQL Server 2008 R2.  The better architecture would be to go with EBS but I wanted to explore the database upgrade and do the EBS later.

Upgrade Steps:

1-Close SharePoint and Close SQL Server Management Studio.

2-Open Command Prompt and run the following command from the location where the SQL Server installation files are available to start the installation (upgrade) of SQL Server R2.

Setup.exe SKUUPGRADE=1

3-Proceed through the installation wizard and on the Installation window click on ‘Upgrade from SQL Server 2000, SQL Server 2005 or SQL Server 2008. 

4-Proceed through the wizard until you get to the ‘Select Instance’ page.

Remember when we inspected our database details in Central Admin we were able to see the SQL instance:

5-Now, in the SQL Upgrade wizard you have to select the same instance. You can see from the screenshot below that my “SHAREPOINT” instance was originally installed as ‘Express’ so this is the one which I have to select to be upgraded to SQL Server 2008 R2. 

6-Click on Next and continue through the wizard….When you get to the “Instance Configuration” page pay careful attention to ensure that all the details are correct.

8-Click on Next and proceed through all the pages to complete the all the installation wizard steps.

9-Once completed review the results to confirm that the upgrade was successful.

 

10-After this perform an IISReset.

Verify Success:

I viewed the properties of my upgraded database and immediately noticed that the space available was increased from 0.13MB to 1.02MB.

Also, after this I was able to add more content to SharePoint and grow the database size beyond 4GB..

Enjoy!!

Thanks to Todd Klindt - http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=55 for his contributions.