none
How to find sharepoint( 2010) content database status whether it is stopped or started using powershell?

    Question

  • Hi,

    i am trying to get the status of content database whether it is stopped or started using powershell.

    Please help me in this regard, how to find this using powershell?

     

    Wednesday, June 23, 2010 6:41 AM

Answers

  • OK...I apologize. I think I misunderstood your original question. I thought you were looking for the status of the database within SQL Server, which is completely different from what it is in SharePoint.

    So I did some looking at content database status in SharePoint itself, and I've got to say that I'm a little puzzled by what I'm seeing...here's what I've found so far:

    • First of all, SP 2010 is very inconsistent in how it refers to this database status. You'll see this throughout, but here's the first case. On the Manage Content Databases page in the Central Admin site, the "Database Status" column shows statuses for each database in the web application. The status I've seen so far are "Started" and "Offline". But, if you open a content database from this page, the options in the Database Status pulldown menu of the Manage Content Database Settings page are "Ready" and "Offline". As best I can tell, a selection of "Ready" results in the "Started" status on the page above it, but that's pretty confusing. "Offline" does actually map to "Offline", so at least that makes sense.
    • Ok, so then I went digging in the SharePoint 2010 Management Shell to look at PowerShell. First of all, Get-SPContentDatabase is frustrating, b/c it doesn't seem to return that status as a property, at least with the checking I was doing.
    • Now it gets really confusing, because I then turned my attention to the Set-SPContentDatabase cmdlet. The encouraging thing about it is that it takes a "Status" input, which I would think should map to the columns we were talking about in the Central Admin site. When I pulled up the cmdlet's help, I started to get worried. In the cmdlet's help, the following values are listed directly after the name of the Status parameter (which I'm inferring to mean that they are the accepted values): Online | Disabled | Offline | Unprovisioning | Provisioning | Upgrading.
    • But when you do a view of the cmdlet's full Help file (get-help set-spcontentdatabase -full), here's the result:

     -Status <Online | Disabled | Offline | Unprovisioning | Provisioning | Upgrading>
          Specifies the status of the SQL Server database. Set this parameter to Ready to make the database available to host new sites. Set this parameter to Offline to make the database unavailable to host new sites.

          The type must be either of the following: Ready or Offline

          Required?                    false
          Position?                    Named
          Default value
          Accept pipeline input?       True
          Accept wildcard characters?  false

    • So what I don't get is why the help then proceeds to state that "Ready" or "Offline" are the two values that should be passed to set a content database's status
    • This got even more confusing when I tried to actually set a database's status using the cmdlet. When I passed a value of "Offline" to the cmdlet it ran fine, so then I went and checked it in the Central Admin site. On the Manage Content Databases page, the database was now set to a status of "Error"! Ugh. I was able to change it back to "Ready"/"Started" in the CA site w/o problem. So I tried a status of "Ready" via the cmdlet, which returned an error stating that the value was not in the enumeration for this parameter. Finally, I tried a status of "Online", which worked without problem to set the status correctly.

    Long story short, I'm not thrilled w/ what I'm seeing here. The TechNet page for Set-SPContentDatabase is somewhat different from what the Help file states (it only references "Ready" and "Offline" as valid inputs), in addition to all the other confusing info I've seen with this. I would stick to making the change from the Central Administration site, and stay away from using Set-SPContentDatabase to change a content database's status.

    Does that make sense?

    John


    MCTS: WSS v3, MOSS 2007, and SCOM 2007

    Now Available on Amazon - The SharePoint 2007 Disaster Recovery Guide. Coming Soon - The SharePoint 2010 Disaster Recovery Guide.
    My blog: My Central Admin.
    Wednesday, June 23, 2010 8:46 PM
  • I resolved my problem. Here is the solution

    contentdb = webapplication.contentdatabases

    contentdb.status

    • Marked as answer by sboaus Thursday, June 24, 2010 11:37 AM
    • Unmarked as answer by sboaus Thursday, June 24, 2010 11:42 AM
    • Marked as answer by Lily Wu Friday, July 02, 2010 7:45 AM
    Thursday, June 24, 2010 11:37 AM

All replies

  • Did you explore the Database cmdlets?

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

     


    Thanks & Regards,
    Kamlesh | Blog | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 23, 2010 6:58 AM
  • But there is no property to check using these cmdlets whether content database is started or stopped under central administration>application management>manage content databases.
    Wednesday, June 23, 2010 7:15 AM
  • There's a couple of things you can try to get this to work...

    • I don't know if the SharePoint 2010 cmdlet Test-SPContentDatabase will work or not, but its worth a shot. I tried testing this by taking a database offline and running the cmdlet against it, but am having problems with my test VM so I couldn't get it to properly take the database offline for me to try it. My thinking is that if the database is offline, the cmdlet may return an error stating that fact, which may meet your needs. (NOTE: this only works w/ content databases)
    • If that doesn't work, if you're using SQL Server 2008 or 2008 R2 you can use the PowerShell tools available with the product. Below are the steps I did to get to a given database's status in SQL Server:
    1. From the server hosting SQL Server, search for SQLPS.exe from the Start Menu, and open it. (If you want to load the SQL "minishell" components without opening it via the custom script, here's more info: http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx
    2. Here's what you'll need to call to determine the database's status, either directly in the SQLPS.exe window itself or via a script:
      $srv = new-Object microsoft.sqlserver.management.smo.server("(Local)")
      $db = New-Object microsoft.sqlserver.management.smo.database
      $db = $srv.databases.item("WSS_Content")
      $db.status
    3. The value of $db.status should indicate what state the database is in, the Members section of this page has a table listing the values that this can be set to: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.databasestatus.aspx
    John
    MCTS: WSS v3, MOSS 2007, and SCOM 2007

    Now Available on Amazon - The SharePoint 2007 Disaster Recovery Guide. Coming Soon - The SharePoint 2010 Disaster Recovery Guide.
    My blog: My Central Admin.
    Wednesday, June 23, 2010 7:03 PM
  • OK...I apologize. I think I misunderstood your original question. I thought you were looking for the status of the database within SQL Server, which is completely different from what it is in SharePoint.

    So I did some looking at content database status in SharePoint itself, and I've got to say that I'm a little puzzled by what I'm seeing...here's what I've found so far:

    • First of all, SP 2010 is very inconsistent in how it refers to this database status. You'll see this throughout, but here's the first case. On the Manage Content Databases page in the Central Admin site, the "Database Status" column shows statuses for each database in the web application. The status I've seen so far are "Started" and "Offline". But, if you open a content database from this page, the options in the Database Status pulldown menu of the Manage Content Database Settings page are "Ready" and "Offline". As best I can tell, a selection of "Ready" results in the "Started" status on the page above it, but that's pretty confusing. "Offline" does actually map to "Offline", so at least that makes sense.
    • Ok, so then I went digging in the SharePoint 2010 Management Shell to look at PowerShell. First of all, Get-SPContentDatabase is frustrating, b/c it doesn't seem to return that status as a property, at least with the checking I was doing.
    • Now it gets really confusing, because I then turned my attention to the Set-SPContentDatabase cmdlet. The encouraging thing about it is that it takes a "Status" input, which I would think should map to the columns we were talking about in the Central Admin site. When I pulled up the cmdlet's help, I started to get worried. In the cmdlet's help, the following values are listed directly after the name of the Status parameter (which I'm inferring to mean that they are the accepted values): Online | Disabled | Offline | Unprovisioning | Provisioning | Upgrading.
    • But when you do a view of the cmdlet's full Help file (get-help set-spcontentdatabase -full), here's the result:

     -Status <Online | Disabled | Offline | Unprovisioning | Provisioning | Upgrading>
          Specifies the status of the SQL Server database. Set this parameter to Ready to make the database available to host new sites. Set this parameter to Offline to make the database unavailable to host new sites.

          The type must be either of the following: Ready or Offline

          Required?                    false
          Position?                    Named
          Default value
          Accept pipeline input?       True
          Accept wildcard characters?  false

    • So what I don't get is why the help then proceeds to state that "Ready" or "Offline" are the two values that should be passed to set a content database's status
    • This got even more confusing when I tried to actually set a database's status using the cmdlet. When I passed a value of "Offline" to the cmdlet it ran fine, so then I went and checked it in the Central Admin site. On the Manage Content Databases page, the database was now set to a status of "Error"! Ugh. I was able to change it back to "Ready"/"Started" in the CA site w/o problem. So I tried a status of "Ready" via the cmdlet, which returned an error stating that the value was not in the enumeration for this parameter. Finally, I tried a status of "Online", which worked without problem to set the status correctly.

    Long story short, I'm not thrilled w/ what I'm seeing here. The TechNet page for Set-SPContentDatabase is somewhat different from what the Help file states (it only references "Ready" and "Offline" as valid inputs), in addition to all the other confusing info I've seen with this. I would stick to making the change from the Central Administration site, and stay away from using Set-SPContentDatabase to change a content database's status.

    Does that make sense?

    John


    MCTS: WSS v3, MOSS 2007, and SCOM 2007

    Now Available on Amazon - The SharePoint 2007 Disaster Recovery Guide. Coming Soon - The SharePoint 2010 Disaster Recovery Guide.
    My blog: My Central Admin.
    Wednesday, June 23, 2010 8:46 PM
  • I resolved my problem. Here is the solution

    contentdb = webapplication.contentdatabases

    contentdb.status

    • Marked as answer by sboaus Thursday, June 24, 2010 11:37 AM
    • Unmarked as answer by sboaus Thursday, June 24, 2010 11:42 AM
    • Marked as answer by Lily Wu Friday, July 02, 2010 7:45 AM
    Thursday, June 24, 2010 11:37 AM
  • Almost-

    There may be multiple databases so use an index-

     

    $contentdb = $webapplication.ContentDatabases[0]

    $contentdb.Status

     

    and to change status use:

     

    $contentdb.Status = "Disabled"

    $contentdb.Update()

     

    "Disabled" corresponds to Offline in Central Admin

    "Online" corresponds to Ready in Cental Admin

     

    Thanks,

    Dave


    JJBongo
    Tuesday, July 13, 2010 2:28 PM
  • I would like to suggest that it is not possible to use CA in large farms. At list you cannot manage hundreds of databases going through switches in CA. I would take JJBongo’s hint and suggest presuming that you may have many DBs so we do not have relationship one webapp one database.

    It is also true that if you set your database offline you will not be able to list it with switch –WebApplication (Get-SPContentDatabase  -WebApplication  <>)

    So I would suggest using –Identity switch like:

    $cdb1 = get-spcontentdatabase -Identity WSS_Content_80
    $cdb2 = get-spcontentdatabase -Identity WSS_Content_80_2

    PS C:\Windows\system32> $cdb1

    Id               : 1b0f86b9-8241-4eb0-aa97-90a43fc13c5a
    Name             : WSS_Content_80
    WebApplication   : SPWebApplication Name=SharePoint - 80
    Server           : SQL8R2E
    CurrentSiteCount : 1

    PS C:\Windows\system32> $cdb2

    Id               : 8e9bd97f-7a6c-45d4-8123-c390811fb42f
    Name             : WSS_Content_80_2
    WebApplication   : SPWebApplication Name=SharePoint - 80
    Server           : SQL8R2E
    CurrentSiteCount : 1

    Status checking:
    PS C:\Windows\system32> $cdb1.status; $cdb2.status
    Offline
    Offline

    Status setting:
    PS C:\Windows\system32> $cdb1,$cdb2 | Set-SPContentDatabase -Status Online

    Status checking:
    PS C:\Windows\system32> $cdb1.status; $cdb2.status
    Online
    Online

    This works fine and if you need more complex solutions you can build on something similar.

    There is problem in central administration after this you will see Error under Database Status in Manage Content Databases.  If you want to remove it you will need to switch it on and off manually or just give it time.

    • Proposed as answer by Z.KC Friday, January 07, 2011 5:32 AM
    Friday, January 07, 2011 5:19 AM
  • To set your content database offline from a batch:

    %SYSTEMROOT%\System32\WindowsPowerShell\v1.0\PowerShell.exe -NoExit " & ' C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\POWERSHELL\Registration\\sharepoint.ps1 ' " Set-SPContentDatabase -Identity SP_Content_database_name -Status Offline
    
    

    Just change SP_Content_database_name to the name of your content database.  I do this after creating a new database and adding a site to prevent new sites from being created in the database.


    Wednesday, July 13, 2011 4:56 PM