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:

Start all the application pools which are stopped in the SharePoint 2010 Farm (Using PowerShell)

# Start the application pools stopped in the SharePoint 2010 farm
function StartStoppedAppPools
{
   
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $apppool = Get-WmiObject -Namespace root\MicrosoftIISv2 -Class IIsApplicationPoolSetting -ComputerName $server.name -Authentication 6 | Where { $_.AppPoolState -eq '4'} | select name
        if ($apppool -eq $null) {
            Write-Host "No app pools are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        { 
         foreach ($appObj in $apppool)
             {
        $app = $appObj | Out-String
           $apppoolname = $app.Substring($app.LastIndexOf('/') + 1,($app.Length-$app.LastIndexOf('/'))-1).Trim()
             
              if($apppoolname -eq 'SharePoint Web Services Root')
              {
                 Write-Host "App Pool $apppoolname does not require to start in $($server.Name)" -foregroundcolor yellow           
              }
              else{
            Write-Host "Starting the app pool '$($apppoolname)'"
            Get-WmiObject -Namespace 'root\webadministration' -Class ApplicationPool -ComputerName $server.name -Authentication 6 -Filter "Name='$apppoolname'" | Invoke-WmiMethod -Name Start
             
                  Write-Host "App Pool $apppoolname is started on server $($server.Name)" -foregroundcolor green
                 }
             }
        }
    }  
}

Example

Get all the Application Pools which are stopped in the SharePoint 2010 Farm (Using PowerShell)

# Get all the application pools stopped in the SharePoint 2010 farm
function GetAppPoolStatusFarm
{
   
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $apppool = Get-WmiObject -Namespace root\MicrosoftIISv2 -Class IIsApplicationPoolSetting -ComputerName $server.name -Authentication 6 | Where { $_.AppPoolState -eq '4'} | select name
       
        if ($apppool -eq $null) {
            Write-Host "No app pools are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        { 
            foreach ($appObj in $apppool)
             {
                  $app = $appObj | Out-String
               $apppoolname = $app.Substring($app.LastIndexOf('/') + 1,($app.Length-$app.LastIndexOf('/'))-1).Trim()
                 
                  if($apppoolname -eq 'SharePoint Web Services Root')
                  {
                      Write-Host "App Pool '$($apppoolname)' is stopped on server $($server.Name)" -foregroundcolor yellow
                  }
                  else
                  {
                    Write-Host "App Pool '$($apppoolname)' is stopped on server $($server.Name)" -foregroundcolor red
                  }
             }
        }
      
    }  
}

Example

Restart IIS in all servers in a SharePoint 2010 farm

Step 1. Create a folder with 2 powershell blank files as below image.

 

Step 2. Open Sam.Functions.PS1 file and copy the below code snippet.

# Stop specified service on all servers within the farm

function Stop-ServiceOnFarm

{

    param ($ServiceName)

     

    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )

    {

        $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"

         

        if ($service -eq $null -or $service.State -eq "Stopped") {

            continue

        }

         

        Write-Host "Stopping '$($ServiceName)' on $($server.Name)..."

        $service.InvokeMethod('StopService',$Null)

         

        do {

            Start-Sleep -s 5

            $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"   

        } while ($service.State -ne "Stopped")

    }  

}

# Starts specified service on all servers within the farm

function Start-ServiceOnFarm

{

    param ($ServiceName)

     

     foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )

     {

        $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"

         

        if ($service -eq $null -or $service.State -eq "Started" -or $service.State -eq "Starting") {

            continue

        }

         

        Write-Host "Starting '$($ServiceName)' on $($server.Name)..."

        $service.InvokeMethod('StartService',$Null)

     }

}

# Restarts IIS on all servers within the farm

function Restart-IIS

{

    Stop-ServiceOnFarm "W3SVC"

    Stop-ServiceOnFarm "SMTPSVC"

    Stop-ServiceOnFarm "IISADMIN"

     

   Start-ServiceOnFarm "IISADMIN"

   Start-ServiceOnFarm "SMTPSVC"

   Start-ServiceOnFarm "W3SVC"

}

 

Step 3. Open the file Sam.RestartIISOnFarm and copy the below snippet.

 

  if ($SCRIPT:scriptDir -eq $null -or $SCRIPT:scriptDir -eq "")

{

 $SCRIPT:scriptDir = (Get-Location -PSProvider FileSystem).ProviderPath

}

# ** Load My functions

Write-Host "* Loading Functions..."

. (Join-Path $scriptDir "Sam.Functions.ps1")

Restart-IIS

Step 4. Close and Save the files and call the .\Sam.RestartIISOnFarm.ps1 file from any server in the farm.

Merge SharePoint Log files in a farm

As we know that SharePoint logs are generated in all servers in a farm. So if you would like to find the log for your application issue, you may need to look into the log in all WFE/Index servers. In that case you do not need to open logfile from each server rather you can use Merge-SPLogFile powershell command in one server to get the ULS logs for the entire farm and investigate it.

Example:

Once the logfile got generated and you can open in UlsViewer tool to epen the logfile.

One intersting thing is that you can also filter logfiles by specifying ‘second’ duration in the command. This is really required as thousands of logs got created in a minute.

To know more in details please refer the below link

http://technet.microsoft.com/en-us/library/ff607721.aspx

 

Developing Quiz Test Module in OOB of SharePoint

If you have any requirement like you need to organize a quiz/exam test for set of users, then you do not need to develop any custom web part or no need to buy any third party product. Here I am going to explain step-by-step process to develop of a Quiz Test Module in OOB of SharePoint.

         Step 1.  Create a Custom List with some columns as below.

Column Name	Data Type	Default Value	Required	Hide Field
Q1. Which Country has largest population?	Choice Values [INDIA, UK, USA]	[Make it Blank]	Yes	False
A1	Single Line Text	INDIA	No	True
Q2. Which Country is more powerful?	Choice Values [CHINA, JAPAN, USA]	[Make it Blank]	Yes	False
A2	Single Line Text	JAPAN	No	True
Q3. Which Country is rich?	Choice Values [PAKISTAN, INDIA, SRILANKA]	[Make it Blank]	Yes	False
A3	Single Line Text	INDIA	No	True
Q4. Which Country is harvesting rice more?	Choice Values [SA, AUSTRALIA , INDIA]	[Make it Blank]	Yes	False
A4	Single Line Text	INDIA	No	True
Q5. Which Country is strong in Missile Manufacturing?	Choice Values [RUSSIA, INDIA, USA, JAPAN]	[Make it Blank]	Yes	False
A5	Single Line Text	USA	No	False

        Note: Here I created 5 questions and their 5 answers, so in total I created 10 columns for all.

            

             Step 2. Now enable content type for the List and hide the answer fields (A1, A2, A3, A4, and A5).

             Step 3. Now we will create 3 more fields in the same list which are Score, Final Score and Result.

  

Column Name	Data Type	Formula	Returned Data Type	Required	Test Output
Score	Calculated (calculation based on other columns)	=SUM(IF(EXACT([Q1. Which Country has largest population?] ,A1),1,0),IF(EXACT([Q2. Which Country is more powerful?],A2),1,0),IF(EXACT([Q3. Which Country is rich?],A3),1,0),IF(EXACT([Q4. Which Country is harvesting rice more?],A4),1,0),IF(EXACT([Q5. Which Country is strong in Missile Manufacturing?],A5),1,0))	Number	No	1/2/3/4/5
Final Score	Calculated (calculation based on other columns)	=CONCATENATE(Score,"/5")	Single line of text	No	1/5 Or 2/5 Or 3/5 Or 4/5 Or 5/5
Result	Calculated (calculation based on other columns)	=IF(Score>3,"PASS","FAIL")	Single line of text	No	PASS/FAIL

               

         Now if you look at the List Columns which will looks like as below.

           Step 4: Create New Item to start the Quiz Test and the screen will look like below.

           Step 5: Here is the All Users Result Report which you can also customize depending upon your requirement.

     

For individual user’s result which user can see after completing test, for that you can create another view with some filter (Created By = [Me]) and customizing the view and can get like the below output.

      

        Enjoy It !