We were recently contacted by our long-time customer, Osprey Corporation, because their operations management application that we had developed for them wasn’t accessible by any of the staff, bringing production to a complete halt. They were getting a nasty IIS error page saying “This operation can be performed only on a computer that is joined to a server farm by users who have permissions in SQL Server to read from the configuration database. To connect this server to the server farm, use the SharePoint Products Configuration Wizard, located on the Start menu in Microsoft SharePoint 2010 Products.”
Searching the internet based on that error provided a wide range of paths to go down, so I figured I’d spot check a few places I’ve known to be issues when the database is at play. First, I checked the file system to be sure the database server’s drives were not full (this can sometimes happen if the database/logs grow un-checked), but this wasn’t the case. Next, I pulled up SQL Server Management Studio to have a look at the databases to look for any connectivity issues, and I noticed that quite a few of the databases didn’t have the option to expand them to view tables/views/etc… After reviewing the Windows Application Logs on the SQL Server, I found the true culprit – “Operating system error 2 (The system cannot find the file specified.) occurred while creating or opening the file ‘PATH TO FILE\FILENAME.LDF'” – I checked the folder where all SQL log files were kept, and most of the LDF files for their databases were missing! Under normal circumstances, we would have just restored from the 3AM nightly backup (the issue was discovered early enough where they wouldn’t have lost any data), but that wasn’t an option due to their infrastructure partner not having backups available. A few minutes of further research yielded a very handy command that will actually add a database even if the log file is missing.
Below were the steps I had to take before the command would work successfully. After running the command for all affected databases, their system came back online, and things were back-to-normal.
1. Take the database “offline” in SQL Server Management Studio.
2. Detach the database.
3. Run the following command in a new query window against the “master” database:
CREATE DATABASE [DATABASE_NAME_HERE] ON (FILENAME = ‘PATH_TO_FILE\FILENAME.mdf’)