SharePoint Kings: Run SQL Queries and Fetch List Data from SharePoint Database (WSS_Content) Part-2

Standard

For once, short and simple steps showing how-to query Sharepoint lists directly from SQL.

SharePoint Kings: Run SQL Queries and Fetch List Data from SharePoint Database (WSS_Content) Part-2

Vicious circle : How to disconnect MOSS connections to SQL database without shutting whole Sharepoint down

Standard

Today I woke up to discover that 2 of my 30 Sharepoint content databases on SQL2005 were locked in a “single-user mode” process, and did not get back into multi-users mode as expected every night after the backup ran.
The user were experiencing on the sites using these DB a “An unexpected error has occurred” message, as I didn’t display more explicit custom error in web.config (not to scared users).

I called the SQL Admin guys for process and they confirmed that they only need to unlock it and switch back the status to Multi-users mode.
As even though the sites are not accessible the Front end and Index servers are still making lots of request to the database, and killing the connected users from SQL will not solve it as they will reconnect as soon as they are killed :-(

Problem was :
I have another 28 databases with users connected which meant 8 web apps, 28 sites collections and about 40 to 60 sub-sites.
So for only 2 site collections connecting to these 2 databases I was going to stop access to the other 26, even if only for a few minutes it was not really my idea.

So I decided to only take these 2 site collections and see if I can stop the access to their associated databases only, without touching the rest on Sharepoint.
The solution is simple :

  1. from the Sharepoint server : detach the content database from the site collection
  2. from the SQL box: fix the DB (back in multi-users mode in my case)
  3. form the Sharepoint box: re-attach the content db to its site collection.

Voila.

Just make sure that prior to detaching the DB at step 1, you also do a “prepare move” so that the database “doesn’t get too scared” when you detach it ;-)

STSADM steps are below :

1- preparetomove (tell server to be cool)
stsadm -o preparetomove -contentdb
SQDB-Server1:WSS_Content_DB1 -site http://webapp.mysharepoint.com/sites/sitecoll1

2- detach db (“deletecontentdb”)
stsadm -o deletecontentdb -url http://webapp.mysharepoint.com/sites/sitecoll1 -databaseserver SQDB-Server1 -databasename
WSS_Content_DB1

3-attach db (“addcontentdb)
stsadm -o addcontentdb -url
http://webapp.mysharepoint.com/sites/sitecoll1 -databasename WSS_Content_DB1 -databaseserver SQDB-Server1

Dabble DB: If only Sharepoint was looking like this !

Standard

Check this online database to share data, all what Sharepoint does but it a much faster Ajax way !
You wish you had this for your corporate environment! :-(

Dabble DB: Explore Dabble

[update]

On June 10, 2010, Dabble DB announced[1] that their parent company, Smallthought Systems, had been acquired by Twitter.[2]

On March 17, 2011, Dabble DB announced[3] that the Dabble DB service would be shut down on May 18, 2011.

[/update]