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
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 |
If the Fetch Farm Measures flag of these tests is set to No or to If Central Administration Site, then this test should be configured with the credentials of a user with the following privileges:
On the other hand, if the Fetch Farm Measures flag of these tests is set to Yes, then the user configured for the tests not only requires the four privileges discussed above, but should also be part of the following 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. |
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:
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:
|
||||||
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:
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:
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. |