I have been working with SharePoint for a long time. An error was encountered not long ago that I have never seen when patching a SharePoint server (and unfortunately I have seen a lot of errors over the years). While running the configuration manager I received the following error:
“An exception of type System.Data.SqlClient.SqlException was thrown. Additional exception information: Invalid object name ‘Webs’.”
The error of course was really nice and pointed me to the log file to get more information. Opening up the log file and searching for ERR (put space at the end to have a better chance of finding an error entry instead of other words containing those letters) I was expecting loads of information to be made available to me so I could easily fix this problem. I really should have known better as I received the following:
At no point in the logs did it tell me where this problem was occurring. So where do we go from here?
Well let’s dig into it shall we?
Determining the Source of the Problem
So after some research I found out that the error message indicates one (possibly more) of our content databases was missing some views. It may just be this one, or it could be more. However, the log file doesn’t tell you which database is broken. If you have a large farm going through each database to find the problematic one can be very time consuming.
Enter PowerShell. Use the following script to find any databases that have issues. You will be using Test-SPContentDatabase to scan the DB for problems.
$spWebApp = Get-SPWebApplication <Web App URL> $contentDBs = $spWebApp.ContentDatabases foreach($contentDB in $contentDBs) { Write-Host ("Testing contentDB: {0}" -f $contentDB.Name) -ForegroundColor Cyan; Test-SPContentDatabase -Name $contentDB.Name -WebApplication $spWebApp.Name }
The script loops through every content database attached to your Web Application and scans them for issues. When I ran it in my environment I received the following results:
As you can see only one DB is indicating there is a problem. This is good as it will save time for the fix. Hopefully you don’t have many yourself.
Viewing the database via SQL Management Studio, we can see that it is missing all the views (I have expanded another content database for comparison)
Resolving the Problem
Fixing the issue is pretty straight forward.
WARNING: Making updates to a production server database is not recommended as it can void your warranty. It is suggested to discuss with Microsoft support before proceeding with the following fix in your production environment
You simply need to recreate the views in your bad database. You will do this by using the views in a content database that is working fine. Right click on a view and select: “Script View As” -> “Create To” -> “New Query Editor Window”
Next update the USE statement by changing it to the name of the database that is missing the view(s) and run the query.
Repeat this for all of the missing views.
After this is complete, rerun your configuration process and as long as there is nothing else wrong in your environment you should be good to go!
Thanks for reading!!
Comments
we got this same issue in our UAT environment and thought it was a one off, we then got the same issue in our prod. issue is that the db restore we did in UAT is now not so simple in Prod unless we want to lose some data. When you logged a call with MS this is the approved approach they advised?
I cannot say I am afraid. I never logged a call with Microsoft since the problem was in our Dev environment. As I said Microsoft recommends no direct modification of your database. In fact, you will likely void your warranty. However, I have worked with Microsoft in other instances and with their assistance you can update the database. If my environment having this issue had been a production environment I would not have performed the updates without first ensuring with Microsoft the updates are acceptable. I strongly suggest you do the same.
Great article, fixed my dev. environment using this article. This is the first time I’ve ever seen this issue.
Glad it helped Geoffrey. Have a great day!!