Saturday, April 21, 2007

Sharepoint and databases

Well funny thing happened these week, I wanted to create a (sub)web programmatically using the OM using a webtemplate. Nothing fancy here eh? :) Just what I thought so I build it and I wanted to test it and everyhing went ok. So I deployed on production environment, tested it there and then the problems came. It continually keep getting errors like 'cannot complete this action'. Things that did work :
1. The ID could be read via the OM
2. The title of the web could be read via the OM
Things that didn't work :
1. Kept getting 404's when I navigated to the site
2. Kept getting 'cannot complete this action' errors using the weburl and adding the /layouts/settings.aspx (or any other .aspx) urls
3. The navigation in WSS was broken, all the subsites could not be displayed.
4. Could not be deleted using the OM
5. Could not be deleted using STSADM

So what to do? Yes.. the one thing you should never do. Modify the database. It's unsupported by MS (default.aspx-scid=kb;EN-US;841057) as my mate Daniel also pointed out and told me to call support of MS to solve this issue. Although during our IM session I managed to fix it by modifying the record in the WEBS table and then I could delete the web using STSADM. The tricky part here is that you'd never know what happens in the future after this modification (although the records were gone after the deletion with STSADM, so technically speaking the modifications were not there anymore)

This brings me to the next thing I wanted to get of my chest and that is: Sharepoint developers/consultants must know what is going on in the databases. Having read the following blogpost SharePoint Database Growth Rates by Dave Wollerman about truncate the logfiles if the recovery model of the database is set to Full. By default the databases that are created by Sharepoint have the recovery model on Full. And why is that? I can't imagine why anyone wants to recover his/her Sharepoint database using the transactional logs since the database backup is probably the last backup/restore method you should use.
Therefore I always recommend setting the recovery model to Simple so you don't have to think about the transaction logfiles. Another benefit you get with this is better perfomance since the DMBS doesn't have to worry about creating logfiles. But to know all of this, just to let the DBA guys know how to configure, you have to get knowledge of SQL Server.

So to conclude this post: do some SQL Server training or even better get certified as MS SQL DBA (like I'm planning to be ;))

7 comments:

Anonymous said...

Robin,

I came across your blog since I had experienced the same problems as you did where by one of the subsites I created did not work. To explain how this happened let's take a few steps back. While everything is working fin, and being new to foundation server, I extended foundation server in Sharepoint Central administration, then the first problem occured where I could not connect using VSTS to access Team Projects. After re-running as suggested on MS TFS forum I ran 'htt://[tfs server]:8080/services/v1.0/registration.aspx' which fixed the problem.

With the initial problem solved. Another problem showed up which is that the default root sharepoint site was reset. It turns out that my default top site is hosting multiple subsites. I tried to re-create a subsite from the original site to extract the shared documents but to no avail (more on this later).

Finding no result without backup. I went into the SQL Server to try and find out how things were stored and referenced. A portion of the answer was solved by deleting the recently created root sites.

Now that the old top site is restored. Browsing through all the subsites, one in particular gave me an HTTP 404 error (webpage not found) The subsite shows under sites and workspace still yet it is not loading. Looking back, this particular subsite is the one I tried to recreate when the new top site was created. I wonder what caused this to happen and broke the chain from this subsite?

Nan

Robin Meuré said...

Hi Nan,

I guess the reason is that during the creation of the rootsite something went wrong. Are you getting 'Cannot complete this action' errors as well?

Anonymous said...

Use the Full recovery model on production databases. You can then recover up to the time of failure, where simple will only get you to the time of the backup. The log will not get out of control if you are backing SQL up properly...IE Database and transaction log backups.

Anonymous said...

Hi Robin,

Which field(s) did you modify within the webs table to resolve this?

I'm having this exact same issue in a test environment (thankfully!)

Thank you

Unknown said...

If you want to repair your corrupted SharePoint database due to drive failures, accidental file deletion on WSS websites, drive failures, server downtime, saved backup turned bad, and any other, then I would like to refer user-friendly tool:- http://www.filesrecoverytool.com/sharepoint-database-repair.html


Unknown said...

Excellent SharePoint Database recovery application for recovering database of SharePoint SQL. Tool also successfully supports to retrieve deleted records like: sites with sub-sites.

Download:- http://www.recoverydeletedfiles.com/sharepoint-server-data-recovery.html

digital signature Adobe said...

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