SharePoint Databases Test

Different types of databases are typically installed for SharePoint. These database types are briefly discussed below:

  • Configuration Database:

    The configuration database contains data about the following:

    • SharePoint databases
    • Internet Information Services (IIS) web sites
    • Web applications
    • Trusted solutions
    • Web Part packages
    • Site templates
    • Web applications

    The configuration database also contains specific data for SharePoint farm settings, such as default quota settings and blocked file types.

  • Content Database:

    Content databases store all content for a site collection. This includes site documents or files in document libraries, list data, Web Part properties, audit logs, and sandboxed solutions, in addition to user names and rights.

    All of the files that are stored for a specific site collection are located in one content database on only one server. A content database can be associated with more than one site collection.

    Content databases also store user data for Power Pivot for SharePoint, if you installed it in your SharePoint Server 2013 environment. 

  • Other Databases:

    Service Application databases such as App Management database, Business Data Connectivity database, Search service application database, Secure store service database Usage and Health Data Collection database, and many more are typically grouped under Other Databases.

These databases can grow pretty quickly, and if this growth is not tracked and controlled, users may be left with no space for SharePoint data. SharePoint administrators should hence prudently and proactively plan their data storage needs, accordingly size the databases, and effectively manage the space available in the databases, so that manageability, performance, and reliability issues do not arise. This is where the SharePoint Databases test helps!

Besides reporting the state of each database, this test also monitors the size, usage, and growth of every database, thus pointing administrators to those databases that are over-used or are exhibiting alarming growth patterns! In addition, the test provides hints for enhancing the overall performance of the content databases – will it help to cleanup the orphaned items? should the recycle bin storage space be reduced? should the content database host fewer site collections? 

Target of the test : A Microsoft SharePoint Server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for each database of each type used by the SharePoint Server being monitored

First-level descriptor: Database type

Second-level descriptor: Database name

Configurable parameters for the test
Parameters Description

Test period

This indicates how often should the test be executed.

Host

The host for which the test is to be configured.

Port

The port at which the host server listens.

Fetch Farm Measures

Typicaly, farm-level metrics - eg., metrics on farm status, site collections, usage analytics - will not vary from one SharePoint server in the farm to another. If these metrics are collected and stored in the eG database for each monitored server in the SharePoint farm, it is bound to unnecessarily consume space in the database and increase processing overheads. To avoid this, farm-level metrics collection is by default switched off for the member servers in the SharePoint farm, and enabled only if the server being monitored is provisioned as the Central Administration site. Accordingly, this parameter is set to If Central Administration by default. This default setting ensures that farm-level metrics are collected from and stored in the database for only a single SharePoint server in the farm.  

If you want to completely switch-off farm-level metrics collection for a SharePoint farm, then set this parameter to No.

Some high-security environments may not allow an eG agent to be deployed on the Central Administration site. Administrators of such environments may however require farm-level insights into status and performance. To provide these insights for such environments, you can optionally enable farm-level metrics collection from any monitored member server in the farm, even if that server is not provisioned as the Central Administration site. For this, set this parameter to Yes when configuring this test for that member server. 

Domain, Domain User, Password, and Confirm Password

When monitoring a SharePoint server, this test has to be configured with the credentials of a domain user with the following privileges:

To know how to add a user to one of these groups, refer to Adding a User to Local Groups on the eG Agent Host.

It is recommended that you create a special user for this purpose and assign the aforesaid privileges to him/her. Once such a user is created, specify the domain to which that user belongs in the Domain text box, and then, enter the credentials of the user in the Domain User and Password text boxes. To confirm the password, retype it in the Confirm Password text box.

Measurements made by the test
Measurement Description Measurement Unit Interpretation

Is database in use?

Indicates whether/not this database is in use.

 

The values that this measure can report and their corresponding numeric values are listed in the table below:

Measure Value Numeric Value

Yes

1

No

0

Note:

By default, the measure reports the Measure Values listed in the table above to indicate the usage state of the database. However, in the graph of this measure, the same will be represented using the numeric equivalents only.

Size

Indicates the current size of this database.

GB

The size requirements typically vary with database type.

For instance, Configuration databases are unlikely to grow significantly, but Content databases are prone to rapid growth. Hence, Microsoft recommends that no content database be more than 200 GB in size.

Please refer to the following link: https://technet.microsoft.com/en-IN/library/cc678868.aspx, for detailed information on the size and scaling considerations of the different types of SharePoint databases.

Disk space usage

Indicates the percentage disk space in the SQL server that is used by this database.

Percent

A high value for this measure is a cause for concern, as it indicates excessive disk space consumption by a database.

Compare the value of this measure across databases of a type to identify that database which is eroding the disk space of the SQL server.

Database growth rate

 

Indicates the percentage growth in the size of this database since the last measurement period.

 

Percent

 

A consistent rise in the value of this measure is a sign that the database is growing rapidly!

Such rapid growth trends can be noticed more often in content databases. Since Microsoft recommends that no content database should be more than 200 GB in size, measures should be taken to control the growth of a content database. In this regard, you may want to consider the following measures:

  • Use an ootb Record Center as an archive for old content: The users must manually send each document to the RC using e.g. move and leave a link; note that only the latest major version with metadata is kept – all version history is lost. The information management policies supported by SharePoint for retention and disposition can be used to automate the cleanup. As the RC has its own content databases, the live collaboration databases will grow slower or even shrink as outdated information is moved to the archive. Keeping the live databases small ensures shorter recovery time; while the recovery time for the archived content can be considerable, but not business critical. Search must be configured appropriately to cover both live and archived content.
  • Use a third-party archiving solution for SharePoint. This has the same pros & cons as the previous option, but the functionality is probably better in relation to keeping version history and batch management of outdated content. Search must be configured appropriately to cover both live and archived content.
  • Use a third-party remote blob storage (RBS) solution for SharePoint so that documents are registered in the database, but not stored there. This gives smaller content databases, but more complicated backup and recovery as the content now resides both in databases and on disk. Provided that you don’t lose both at the same time, the recovery time should be shorter. Search will work as before, as all content is still logically in the “database”.
  • The databases size will shrink as data is actually deleted, and backup and recovery is more complicated as content is now both in the database and on disk.
    Search can be configured to also crawl and index the files on disk, but content ranking will suffer as the valuable metadata is lost.
  • Use powershell scripts or other code to implement the disposition of outdated content. The script can e.g. copy old documents to disk and delete old versions from the content database; the drawback being that all metadata will be lost and there is no link left in SharePoint. The databases size will shrink as data is actually deleted, and backup and recovery is more complicated as content is now both in the database and on disk. Search can be configured to also crawl and index the files on disk, but content ranking will suffer as the valuable metadata is lost.

Total orphaned items

Indicates the number of orphaned sites in this content database.

Number

This measure is reported only for ‘Content Databases’.

An Orphaned Site is where SharePoint only has partial information and not a complete set of data for a given site collection in your Windows SharePoint Services or SharePoint Portal Server content databases or configuration databases.  The site may in fact still be viewable via the browser, but you may notice that many things are broken.  

If the Content database growth rate measure is increasing consistently, you may want to check the variations in the value of this measure over the same time period to figure out whether/not the existence of too many orphan sites is contributing to the growth in the size of the content database. If so, you may want to cleanup the orphan sites to right-size your database and to ensure optimum performance.

Site limit

Indicates the maximum number of site collections that this content database can host.

Number

This measure is reported only for ‘Content Databases’.

Microsoft strongly recommends limiting the number of site collections in a content database to 5,000. However, up to 10,000 site collections in a database are supported. Note that in a content database with up to 10,000 total site collections, a maximum of 2,500 of these can be non-Personal site collections. It is possible to support 10,000 Personal site collections if they are the only site collections within the content database.

These limits relate to speed of upgrade. The larger the number of site collections in a database, the slower the upgrade with respect to both database upgrade and site collection upgrades.

The limit on the number of site collections in a database is subordinate to the limit on the size of a content database that has more than one site collection. Therefore, as the number of site collections in a database increases, the average size of the site collections it contains must decrease.

Exceeding the 5,000 site collection limit puts you at risk of longer downtimes during upgrades. If you plan to exceed 5,000 site collections, Microsoft recommends that you have a clear upgrade strategy to address outage length and operations impact, and obtain additional hardware to speed up the software updates and upgrades that affect databases.

Configured site limit usage

Indicates the percentage of the configured site limit that is used by the content database.

Percent

This measure is reported only for ‘Content Databases’.

A value close to 100% indicates that the configured site limit is about to be reached.

By comparing the value of this measure across content databases, you can easily identify the database that hosts too many site collections. You may then have to reassess the ability of that content database to handle additional site collections, and accordingly decide whether to reconfigure the site limit or reduce the number of site collections hosted by the database.

Needs upgrade?

Indicates whether/not this database needs to be upgraded.

 

The values that this measure can report and their corresponding numeric values are listed in the table below:

Measure Value Numeric Value

Yes

1

No

0

Note:

This measure reports the Measure Values listed in the table above to indicate whether/not a database needs an upgrade. In the graph of this measure however, the same is represented using the numeric equivalents only.

Recycle bin storage space

Indicates the space used by the items present in the second stage recycle bin of this database.

MB

Recycle Bins are used to help users protect and recover data. Microsoft SharePoint Server supports two stages of Recycle Bins:

  • First-stage Recycle Bin
  • Second-stage Recycle Bin.

When a user deletes an item, the item is automatically sent to the first-stage Recycle Bin. By default, when an item is deleted from the first-stage Recycle Bin, the item is sent to the second-stage Recycle Bin.

A high value for this measure could indicate that a large amount of deleted data resides in the second stage recycle bin, unnecessarily consuming disk space and increasing the size of the database.

Recycle bin storage space growth rate

Indicates the percentage growth in the space used in the second stage recycle bin of this database, since the last measurement period.

Percent

A consistent increase in the value of this measure indicates that deleted data is steadily accumulating in the recycle bin; this is a cause of concern, as data in the second stage recycle bin can add megabytes to the overall size of the database!

In case of content databases, every site collection has a second stage recycle bin and the size of this bin must not grow beyond 50 percent of the quota set for that site collection. You may want to reduce this percentage to ensure that the recycle bin does not grow too unwieldy and impact the size and performance of the content database.