Archive for category SQL Server
Yesterday my worst fears came true. On my development environment where all my SharePoint 2010 projects are located my Central Administration site suddenly didn’t respond. It only returned HTTP error: 404 – What!!
ISS was running, no problems with AppPool, SQL server was running og the weirdest parts was that all my sites was running as well! Only CA site was down.
So the nasty part began where eventviewers, log file crawling, google searching etc. where analysed in details.
The logs and eventviewers had a lot of
– “Login failed for user ‘INTELLISOFT\sp_workerprocess’. Reason: Failed to open the explicitly specified database.”
but also this
– “During redoing of a logged operation in database ‘SharePoint_AdminContent_00bd9b82-4cf1-4846-975a-2b5b8d5287a5’, an error occurred at log record ID (3173:321:29). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.”
You don’t have to be a SQL Server brain to figure out that this is gonna make your life a nightmare. And on top of all I had just spent several hours updating some Sharepoint solution that needed a blank sitecollection for testing. And with no access til CA, your pretty much out of luck.
So i went to SQL Server Management Studio and noticed that among all my databases the SharePoint_AdminContent_GUID was marked “Suspect”!
After googling this error like crazy the only thing I could find at the beginning was to restore a backup of the AdminContent database. But sadly enough on my dev environment I haven’t got backups of the individual databases. I only backup my Visual Studio projects regular and the entire VMware machine once in a while.
So with many hours of restoration in mind i started copying the last backup from my NAS to the laptop. Meanwile I googled some more and luckily enough I found this article by Mehuil K Bhuva who has just become my biggest idol. Mehuil describes how you run some repair jobs on your corrupt database and in my case the database got restored and is now up and running again.
So Mehuil: From now om I am officially your biggest fan. You just saved me hours and hours of restoring my dev environment and now there is a god chance that I can actually deliver the solution to my customer in time. Thanks again for taking your time to write this amazing article. All I did was to replace the DB name with my own. Run all the SQL commands in one query and after a few minutes the “suspect” mark was gone and CA was running again. Can’t get my arms down – you saved my life!
I decided to write this post so that more people can find Mehuils solution and to let everyone know that it worked for me.
For the complete article on what you should do please visit Mehuils blog.
To make sure that this knowledge isn’t lost I have pasted the SQL Query below but all credit goes to Mehuil on this one – Great work!
1. locate your mdf and ldf files in your SQL Server data directory
2. start up SQL Server Management studio at click “New Query”
3. Insert this below and make sure that you replace database name with your own
–Verify whether Database has any issues
EXEC sp_resetstatus “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c.mdf”
—Alter database and put it on Emergency Mode
ALTER DATABASE “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c” SET EMERGENCY DBCC checkdb(‘SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c’)
–Set the database in the Single User mode
ALTER DATABASE “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c” SET SINGLE_USER WITH ROLLBACK IMMEDIATE
–Repair the database and allow data loss
–Set the database back to Multi-User mode
ALTER DATABASE “SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c” SET MULTI_USER
–Ensure Database is reset
EXEC sp_resetstatus ‘SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c’
Hit execute and wait for a few minutes. It took around 4 minutes on my system.
Once done a refresh of your SQL Server object explorer should remove the “Suspect” marking and your CA site should be running again.
Running my sharepoint developer environment on a virtual machine on my laptop sometimes result in having my drives fill up.
Very often, since I’m not in a production environment I don’t really care about my transaction log files in the sharepoint content databases, and they can grow very large in a short while.
Using SQL Server Management Studio you can easely truncate your logs firing this SQL query, BUT DO SO WITH CARE!
DBCC SHRINKFILE("WSS_ContentDB_log", 1) BACKUP LOG WSS_ContentDB WITH TRUNCATE_ONLY DBCC SHRINKFILE("WSS_ContentDB_log", 1)
DONT EVER DO THIS IN YOUR PRODUCTIONSERVERS!! LEAVE SQL SERVER MANAGEMENT TO DATABASE ADMINISTRATORS