SQL Azure Database Size Test

Periodic monitoring of the usage of the files of each file type in the database is essential to ensure that the files of the database are always adequately sized to handle current and future loads. The SQL Azure Database Size test monitors the usage of the files of the target Microsoft SQL Azure database server, and indicates if it requires resizing.

Target of the test : A Microsoft SQL Azure database server

Agent deploying the test : An external/remote agent

Outputs of the test : One set of results for each file type on the target Microsoft SQL Azure database server being monitored.

Configurable parameters for the test
  1. Test period - How often should the test be executed
  2. Host - The host for which the test is to be configured.
  3. 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.
  4. 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 an Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter.
  5. USER – Provide the name of a SQL user with the view server state role.
  6. password - The password of the specified user.
  7. confirm password - Confirm the password by retyping it
  8. 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.
  9. 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.
  10. 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.
  11. REPORTMANAGERTIME – By default, this flag is set to Yes, indicating that, by default, the detailed diagnosis of this test, if enabled, will report the shutdown and reboot times of the device in the manager’s time zone. If this flag is set to No, then the shutdown and reboot times are shown in the time zone of the system where the agent is running(i.e., the system being managed for agent-based monitoring, and the system on which the remote agent is running - for agentless monitoring).
  12. To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

    The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

    • The eG manager license should allow the detailed diagnosis capability
    • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Allocated size

Indicates the space allocated for this file type.

GB

 

Free space on maximum

Indicates the amount of disk space that is currently available for use by the files of this file type.

GB

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

Percentage free space

Indicates the percentage of Max file size that is currently available for use by the files of this file type.

Percent

If many files of this file type 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.

Growth rate

Indicates the growth rate of the files of this file type.

GB/sec

 

Maximum size

Indicates the maximum size up to which the database files of this file type can grow.

GB

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.

Used size

Indicates the amount of space that was already utilized by the files of this file type.

GB

A high value for this measure indicates that the space in the files are depleting at a faster pace.