PowerShell Script : Get the SharePoint Content Databases which are not mapped to Nintex Database

Here is the PowerShell script to get SharePoint Content Databases which are not mapped to Nintex Database in a SharePoint 2010 farm.


#Load all the assemblies that we need to use          
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SharePoint.Administration') | Out-Null          
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow') | Out-Null          
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Administration') | Out-Null          
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Common') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.ContentDbMappingCollection') | Out-Null          
           
#Add the SharePoint PowerShell snapin (in case it's not already loaded)          
if(-not(Get-PSSnapin | Where-Object {$_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell;          
}          
       
$DBs=Get-SPContentDatabase

foreach ($DB in $DBs)
{

    [Nintex.Workflow.ContentDbMapping]$contentDbMapping;          

    $contentDbMapping = [Nintex.Workflow.ContentDbMappingCollection]::ContentDbMappings.GetContentDbMappingForSPContentDb($DB.Id);

    if($contentDbMapping -eq $null)          
    {          
        Add-Content e:\sam\NMR.txt $DB.Name
    }    
}

Let me know if you have any question.

create database permission denied in database 'master'

If you are getting the below error while creating a new content database in SharePoint 2010, please verify whether you farm service account has dbcreator permission.

You can verify the permission in SQL Management Studio or check with your SQL team.

In my case I have provided the permission and then I am able to create new content database.

How to delete an Orphaned SharePoint database in SharePoint 2010 ?

Note: This needs to be carefully executed after you have tested all other troubleshooting steps.

Scenario: Under View All Site Collections of a web application, you click on the site URLs and it doesn’t show you the details about the site URL, Site Description, Primary Site Collection Administrator etc.

In this case, it could be that the content database was detached from SharePoint and was also requested from SQL to remove it from the SQL instance.

However when you look for site collections it still refers to those deleted site collections from that detached content DB and thus it doesn’t show the details as mentioned above regarding site URL, Site Description, Primary Site Collection Administrator etc. This happens because the presence of that content database is still known to the SP_Config database.

This situation tells me that this DB is now an Orphaned object and needs to be cleaned up. However I cannot simply remove this object from SQL directly because as per Microsoft, anything executed from SQL side in terms of addition/deletion, we would not get any support from them.

So to address this situation without putting our Microsoft support at risk, we can leverage SharePoint PowerShell to achieve it.

The process is simple.

First we try to find this corrupted/orphaned database entry in the SQL SP_Config DB (Objects table)

SELECT * FROM Objects WITH (nolock) where Name = '<your orphaned content database name>'

This should show you an entry. Then you need PowerShell to get this database in an object

$orphanedDB = Get-SPDatabase | where{$_.Name -eq “your orphaned content database name”}

Once you have the object, please verify the object details by executing “$orphanedDB”. It should be like this.

PS C:\Users\sam2015> $orphanedDB = Get-SPDatabase | where{$_.Name -eq “your orphaned content database name”}

PS C:\Users\sam2015> $orphanedDB

Id               : 564d9d82-4f65-4b88-b20b-543ff498e40e7

Name             : “your orphaned content database name”

WebApplication   :

Server           : <<SQL Server Name>>

CurrentSiteCount : 33

So as you see above, the content database is known to the SP_Config DB, however it is not known to any web application

Hence this Orphaned object can be removed as below

$orphanedDB.Delete()

Then you refresh the Central Admin site and you should see all site collections with proper details next to them

Hope this helps!!

How do delete corrupted site collections in SharePoint 2010 ?

Scenario :  Move-SPSite command failed during moving an SharePoint site collection(of size 50 GB) from content database A to Content Database B within same SQL instance.

Description : While moving the site from content database A to Content Database B, if it run for sometime and finally failed due to any reason, then again if you would like to move the site to same content database B, then it may fail. Because content database B is having the entries of the site in it's tables which inserted during the last move-spsite execution.

However if you go to central admin and verify the sites count in the content database B, you will not find any difference, but if you open the Database B in SQL Management Studio and verify the tables, you will find the entries of this site collection. Also if you verify the content database size, it must be increased due to corrupted site collection.

Note: Your original site is safe and accessible from database ''A''.

SQL Query to find the site collection url from content database B

Select FullUrl from Allsites

Problem 1: Content database unnecessarily occupy the space for the corrupted site collection.
Problem 2: Unable to move the same site to content database B.

Resolution:

Solution 1: Verify if the Gradual Site delete timer job helps !

Solution 2: Execute below powershell command to delete the corrupted site from content database B.

$objSite = Get-SPSite "http://test.sam.com/sites/testsite"

$objSiteID = $objSite.Id // although this ID will display from Content Database A, but this ID is also same for the site in content database B

$objContentDB = Get-SPContentDatabase "B"
$objContentDB.ForceDeleteSite($objSiteID, $false, $false) // It will take sometime, wait until it is finished.

Testing:

Now you can verify the same SQL query if it's returning the corrupted site collection(/sites/testsite)

Select FullUrl from Allsites

Also you can ask the SQL team to sync the database ''B'' whose size should be decreased.

Hope this helps !

SharePoint 2010 : The Workbook can not be opened

Scenario 1: 

Error: While opening the excel file in browser, it says "Non of supported extension can be opened"

ULS Log : SharePointFileLoader.GetSPFile: Sharepoint threw a handled exception - turning it into a FileOpen exception. Exception is: Microsoft.SharePoint.Upgrade.SPUpgradeCompatibilityException: There is a compatibility range mismatch between the Web server and database "TEST_SharePointDB", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to the same version and build level to return to compatibility range.     at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.ValidateBackwardsCompatibility()     at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)     at Microsoft.SharePoint.SPSite.GetSPRequest()     at Microsoft.SharePoint.SPSite.get_Request()     at Microsoft.SharePoint.SPSite.SetAllowUnsafeUpdates(Boolean allowUnsafeUpdates)     at Microsoft.Office.Excel.Server.MossHost.SharePointHelperMethods.GetSite(String fileLocation, IClaimsIdentity claimsIdentity)     at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity)

Scenario 2:

Error: While opening the excel file in browser, it says "The Workbook can not be opened"

ULS Log : ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=GenericFileOpenError; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: The workbook cannot be opened. ---> Microsoft.Office.Excel.Server.Host.HostFileException ---> Microsoft.SharePoint.Upgrade.SPUpgradeCompatibilityException: There is a compatibility range mismatch between the Web server and database "TEST_SharePointDB", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to the same version and build level to return to compatibility range.    

 at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.ValidateBackwardsCompatibility()    

 at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)    

 at Microsoft.SharePoint.SPSite.GetSPRequest()    

 at Microsoft.SharePoint.SPSite.get_Request()    

 at Microsoft.SharePoint.SPSite.SetAllowUnsafeUpdates(Boolean allowUnsafeUpdates)    

 at Microsoft.Office.Excel.Server.MossHost.SharePointHelperMethods.GetSite(String fileLocation, IClaimsIdentity claimsIdentity)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity)     -

 -- End of inner exception stack trace ---    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2()    

 at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo)    

 at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext)     -

 -- End of inner exception stack trace ---    

 at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext)    

 at Microsoft.Office.Excel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLocation trustedLocationSettings, Guid requestSiteId, IExcelServerDocumentContext documentContext)    

 at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Boolean newWorkbook, Boolean useCollection, Boolean loadedOnDemand)    

 at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)    

 at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution()    

 at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync()    

 at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareComplete(PrepareAsyncArgs args)

Resolution Step 1 : Service application pool account should have DB Owner permission to site's(/sites/testapp/) content Database.

Resolution Step 2 : Recycle the Excel Calculation server application pool(must be a GUID) in all the servers where the Excel calculation service is started(find from Central Admin).

Start all the stopped websites in SharePoint 2010 farm (Using PowerShell)

# Start all the stopped websites in SharePoint 2010 farm
function StartStoppedWebSitesInFarm
{
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $websites = Get-WmiObject -namespace "root/webadministration" -class Site -ComputerName $server.name -Authentication 6 | Where {$_.GetState().ReturnValue -ne 1} | select name
     
        if ($websites -eq $null) {
            Write-Host "No web sites are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        {
            foreach ($webObj in $websites)
             {
                $web = $webObj.name
             
                if($web -eq 'Default Web Site')
                {
                   Write-Host "Default Web Site is stopped in $($server.Name), so skipping this server" -foregroundcolor yellow    
                   break  
                }
                else
                {        
                    Get-WmiObject -Namespace 'root\webadministration' -Class Site -ComputerName $server.name -Authentication 6 -Filter "Name='$web'" | Invoke-WmiMethod -Name Start
                    Write-Host "web site $web is started on server $($server.Name)" -foregroundcolor green
                }
             }
        }
     
    }
}

example:

Get all the web sites which are stopped in the SharePoint 2010 Farm (Using PowerShell)

# Get all the websites stopped in the SharePoint 2010 farm
function GetStoppedWebSitesInFarm
{
   
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $websites = Get-WmiObject -namespace "root/webadministration" -class Site -ComputerName $server.name -Authentication 6 | Where {$_.GetState().ReturnValue -ne 1} | select name
        if ($websites -eq $null) {
            Write-Host "No web sites are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        { 
            foreach ($web in $websites)
             {
                Write-Host "web site '$($web)' is stopped on server $($server.Name)" -foregroundcolor red
             }
        }
      
    }  
}
Example: