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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s