SQL Database Space by File Groups Test

Typically, SQL Server databases have three types of files, as shown in the following table. 

File Description

Primary

The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.

Secondary

Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow.

The recommended file name extension for secondary data files is .ndf.

Transaction Log

The transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.

All data files are stored in the filegroups listed in the following table.

Filegroup Description

Primary

The filegroup that contains the primary file. All system tables are allocated to the primary filegroup.

User-defined

Any filegroup that is specifically created by the user when the user first creates or later modifies the database.

If even a single file group in a database runs out of free space, serious performance degradations will be noticed in applications that depend on that file group for their data needs. To avoid this, administrators must track space usage both at the database-level and at the individual file group-level and proactively identify those file groups that are over-utilized. The SQL Database Space test provides administrators with both these usage insights. By monitoring the space usage in each SQL database, the test points administrators to those databases that are consuming too much space and reveals the type of data that is hogging space – data in tables? Or indexes? Alongside, the test also reports usage metrics for each file group in every SQL database, and accurately pinpoints those file groups that may soon fill up the disk! This way, the test turns the spotlight on databases and file groups that may have to be resized to ensure peak application performance.

Note:

This test will report metrics for file groups only if Microsoft SQL Server 2008 R2 (and above) is monitored.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL server database and every file group in each database

  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. ssl – If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No.
  5. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter.

  6. USER – If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server.

  7. password - The password of the specified user
  8. confirm password - Confirm the password by retyping it.
  9. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the DOMAIN text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server.
  10. isntlmv2 - In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host.
  11. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
  12. excludedb - Specify a comma-separated list of databases for which the space computation need not be done (e.g., temp). The default value is ‘none’.
  13. useperfmon – By default, this flag is set to Yes, indicating that this test uses the Windows Perfmon utility by default to pull out the metrics of interest. To instruct the test to use queries for metrics collection and not Perfmon, set this flag to No. Typically, when monitoring a Microsoft SQL server in an agent-based manner, its best to go with the default setting – i.e., use Perfmon for metrics collection. However, when monitoring the Microsoft SQL server in an agentless manner, its ideal to use queries instead of Perfmon to collect the required metrics. In such cases, set this flag to No. 
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Total size:

The space allocated to a database.

MB

The Total size does not include the size of the database log files.

This measure is reported only for a database.

Reserved space percent:

The percentage of allocated space reserved for tables and indexes of a database

Percent

If the value of this measure reaches 100%, it indicates that the total space in the database has been completely allocated. New tables/indexes can be added to the database, only if its total size is increased.

This measure is reported only for a database.

Reserved space:

The amount of allocated space reserved for the tables and indexes created on a database

MB

If the value of this measure becomes equal to that of the Total size measure, new tables/indexes can no longer be created on the database. To create new tables, you must increase the database size.

This measure is reported only for a database.

Data space:

The amount of allocated space used by data in tables.

MB

The total allocated space that is in use in a database is the sum of the value of the Data space and Index space measures.

This measure is reported only for a database.

Index space:

The amount of allocated space used by indexes.

MB

Unused space:

The amount of allocated space that is available for use in the database

MB

This is the difference between the Total size and Reserved space.

This measure is reported only for a database.

Is auto-growth enabled?

Indicates whether/not auto-growth is enabled for this file group.

Boolean

This measure is reported only for file groups.

Auto-growth is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file grows is based on the settings that you have for the file growth options for the data file.

If this setting is enabled for even one file in a file group, this measure will report the value Yes. If this setting is not enabled for any of the database files in a file group, then this measure will report the value No.

The numeric values that correspond to the above-mentioned measure values are listed in the table below:  

Measure Value Numeric Value

Yes

 100

No

 0

Note:

Typically, this measure reports the Measure Values listed in the table above as the status of the Autogrowth setting. In the graph of this measure however, the status will be represented using the numeric values – i.e., 100 and 0.

Max file size:

Indicates the maximum size up to which the database files in this file group can grow.

MB

This measure is reported only for file groups.

Each database file that is associated with your database has an auto-growth setting. There are three different settings you can use to identify how your database files will grow. They can grow by a specific size, a percentage of the current size, or not grow at all. Additionally you can set your files to unrestricted growth, which means they will keep growing as they need more space or you run out of disk space. Or you can restrict the growth of a database file to grow no larger than a specified size. Each one of these different auto-grow settings have defaults, or you can set them for each database file.

If the auto-growth setting is not enabled at all for a file in a file group, then the amount of space that was originally allocated to that file will be regarded as the Max file size of that file.

On the other hand, if the Auto-growth setting is enabled for a file in the file group, then the Max file size of that file will be one of the following:

  • If a specific size limit is explicitly set for the file, then this will be considered as the Max file size of that file.
  • If no size limit is set for the file, then the total capacity of the disk drive in which that file resides will be considered as the Max file size of that file.

So, if a file group consists of a few data files for which auto-growth is enabled and a few others for which it is disabled, then the Max file size of that file group will be a sum total of the following:

  • The sum of the space allocated to each of the files for which auto-growth is not enabled;
  • The sum of the maximum size limits, if defined, for each file for which auto-growth is disabled;
  • The sum of the total capacity of the disks containing the auto-growth-enabled files for which no size limit is defined.  

Free space:

Indicates the amount of disk space that is currently available for use for this file group.

MB

This measure is reported only for file groups.

A high value implies that the database files in the file group have adequate disk space for growth.  If the value of this measure is low, then you may have to fine-tune your auto-growth settings accordingly.

Percentage of free space:

Indicates the percentage of Max file size that is currently available for use for this file group.

Percent

This measure is reported only for file groups.

This measure reports the Free disk space value as a percentage of Max file size. In other words:

Free disk space/Max file size * 100

If many files in a file group are set to auto-grow till disk capacity is reached, then, a high value of this measure indicates that there is enough space for the files to grow. A low value indicates that there is very little room for file growth.