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 :
- from the Sharepoint server : detach the content database from the site collection
- from the SQL box: fix the DB (back in multi-users mode in my case)
- 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