Wednesday, April 13, 2011

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.

image

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.

image

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

image

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)

image

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

image

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. 

image

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:

image

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. 

image

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.

image

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.

 image

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.

image

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

image

Enjoy!!

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

15 comments:

Christopher said...

Helpful article Johan.

SharePoint Engine said...

Tremendous blog post, loads of beneficial information. I am about to show my buddies and ask them what they think.

Sharepoint Staffing

John Doe said...

I need to upgrade from 2008 R2 express to standard. Where's the best place to buy this from? Microsoft does not make this so simple.

Anonymous said...

This works GREAT!!! Thanks a bunch.

@nu said...

Great job!

Anonymous said...

Hi

I am getting the following error and don't know what to do:

Error
Internal server error exception:

Troubleshoot issues with Microsoft SharePoint Foundation.

Correlation ID: d3ce3b13-e69d-44f1-b7c7-ec8db3dfdc12

Date and Time: 2012/02/10 04:21:57 PM

sercan said...

It's helpful. Thank you.

Anonymous said...

Make sure you choose RBS and not EBS:

http://programming4.us/database/2938.aspx

raksitha B said...

I actually enjoyed reading through this posting.Many thanks.










Sharepoint Remote Blob Storage

Anonymous said...

Hi
I use SharePoint2010 Fundation with Search server 2010 and SQL 2008R2. SQL 2008R2 has limit 10GB but in logs In logs I get error (Error, EventID 2590) that database for aplication_service_search_CrawlStoreDB_11ab40f192f443aa8026f3a2c35e274f has size about 4GB limited by SQL Express. Why? I use SQL 2008 R2 and limit should be 10GB. How to increase this database?
Thank you for help.
Tomasz

Octavio M said...

I been resolve my problem with your tips. Thank you for help.

digital signature said...

Excellent! Thanks for this - I've been looking at this feature for ages. Followed your instructions and it works a treat!

BADBOY said...

You can disable de recycle Bin to Hard delete osbolete files or made space for folder and files changes on SharePoint foundation sites
https://technet.microsoft.com/en-us/library/cc287766(v=office.14).aspx

B.
https://bad3000.blogspot.com

Inam Ahmed said...

WOW, Thanks alot

Henry Gill said...

Getting SQL Error is really terrible. I have come across same situation after which whole activity gets interrupted but going through easy solution:-http://en.mssqldatabaserepair.org/ I solve the issue.

Post a Comment