If I give SQL server name, I should get site status of read , no access and not locked sites status resided in all databases from that particular SQL server
and I should get in excel format. Please help me with the script. Thank you.
If I give SQL server name, I should get site status of read , no access and not locked sites status resided in all databases from that particular SQL server
and I should get in excel format. Please help me with the script. Thank you.
Hi, @sns1 ,
Here is a sample script for you to try.
$dbs=Get-SPContentDatabase | where-object {$_.Server -eq '<SQL Server Name>'}
foreach($db in $dbs)
{
$sites=Get-SPSite -ContentDatabase $db.id | foreach {
#Check Lock Status
#No Locks Applied?
if ($_.ReadOnly -eq $false -and $_.ReadLocked -eq $false -and $_.WriteLocked -eq $false)
{
$Result ="Unlocked"
}
#Read-only Lock?
elseif ($_.ReadOnly -eq $true -and $_.ReadLocked -eq $false -and $_.WriteLocked -eq $true)
{
$Result = "Read-Only"
}
#Adding Content Prevented?
elseif ($_.WriteLocked -eq $true -and $_.ReadLocked -eq $false -and $_.ReadOnly -eq $false)
{
$Result = "Adding Content Prevented"
}
#No Access?
elseif ($_.ReadOnly -eq $null -and $_.ReadLocked -eq $null -and $_.WriteLocked -eq $null)
{
$Result="No Access"
}
$SizeInGB = [System.Math]::Round((($_.Usage.Storage)/1GB),2)
#Write the Result to CSV file separeted with Tab character
$_.RootWeb.Title +"`t" + $_.URL+"`t"+ $SizeInGB + "`t" + $Result | Out-File LockStatus.csv -Append
}
}
Reference:https://www.sharepointdiary.com/2012/12/check-lock-status-for-all-site.html
Updated Script to list all the site collections and the content database it belongs to.
$dbs=Get-SPContentDatabase
foreach($db in $dbs)
{
$sites=Get-SPSite -ContentDatabase $db.id | foreach {
#Check Lock Status
#No Locks Applied?
if ($_.ReadOnly -eq $false -and $_.ReadLocked -eq $false -and $_.WriteLocked -eq $false)
{
$Result ="Unlocked"
}
#Read-only Lock?
elseif ($_.ReadOnly -eq $true -and $_.ReadLocked -eq $false -and $_.WriteLocked -eq $true)
{
$Result = "Read-Only"
}
#Adding Content Prevented?
elseif ($_.WriteLocked -eq $true -and $_.ReadLocked -eq $false -and $_.ReadOnly -eq $false)
{
$Result = "Adding Content Prevented"
}
#No Access?
elseif ($_.ReadOnly -eq $null -and $_.ReadLocked -eq $null -and $_.WriteLocked -eq $null)
{
$Result="No Access"
}
$SizeInGB = [System.Math]::Round((($_.Usage.Storage)/1GB),2)
#Write the Result to CSV file separeted with Tab character
$_.RootWeb.Title +"`t" + $_.URL+"`t"+ $SizeInGB+"`t"+$db.name + "`t" + $Result | Out-File LockStatus.csv -Append
}
}
Note: Two things to mention about the script.
1. The script outputs the csv file in the current directory in PowerShell. 
2. Don't forget to change the SQL server name .
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
HI Jerry Xu,
Ideally I should get bunch of sites list, but I got only 1 site in the output of excel file. Please suggest what could be the wrong.
also can I get each site sizes I same output?
Hi, @sns1 ,
According to your requirements, the script only lists all the site collections belong to content databases in a certain SQL server. You can have a check on the SQL server referring to.
Get-SPContentDatabase | where-object {$_.Server -eq '<SQL Server Name>'}
This cmdlet will return all the content databases inside the specific SQL server. And this cmdlet
Get-SPSite -Limit All | Format-Table -Property URL,ContentDatabase
will list all the site collections with the content database they belong. You can have a check on that. There are possibilities there are content databases in other different sql servers.
I have updated the script to add the site storage size in GB.
Hi Jerry Xu,
I understood, But when I executed the script it supposed to show all the sites from respective SQL server but appears to be some are missing.
Please find attached screenshots. In my case SQL server name is SPSQLP14, I should get all the sites and databases from this SPSQLP14. However I got only 1 db as output although there are other dbs hanging out in spsqlp14 server
Please help further. Thank you. 


6 people are following this question.