ServiceNow Database Size - Estimate the Size of a Database

tiagomacul · ‎08-30-2022

How to identify Instance database size?

Image	Description
	01. Navigate to Instance Usage > Application Usage Overview
	02. Scroll down to Additional Metrics > Primary DB size (MB).

KB0724212

Database size, the sum of all the tables and table index data stored in the instance database server. It’s easy to ask ServiceNow directly or fill out their self service form on the Support page, but even easier to just make a report and put in on a dashboard yourself!

Image	Description
	01. Navigate to Reports > Create New
	02. Create Report
	03. Set a name like “Current Database Size (MB)”, and select the table UA Instance Info [ua_instance_state].
	04. Select type as Speedometer (you can also do single score, but it’s nice to set ranges!)
	05.On the configure tab, select Aggregation as Sum and field as “Primary database size (MB)”
	06. On the style tab, you can set the direction as Minimize, and set reasonable ranges you think your database size should fall under.

Monitoring Series: Database Size Report

Archive table size limits
Table Attribute	Size
Single table static size limit	65535 bytes
Row size limit	8126 bytes
Columns	1000 for Oracle 1011 for others

Data archive table size limits

Tables [sys_db_object]: Contains a record for each table.
Dictionary Entries [sys_dictionary]: Contains additional details for each table and the definition for every column on each table. Each row represents either a column on a table or a table.

Data dictionary tables

KB0749585 - Tables & Dictionary - Frequently asked Questions (FAQ)

Calculate database size

Calculate the order-preserving and tokenization database size

KB0819668 - How to view database footprint of an instance on Now Support

Following script will list files in sys_attachment table that is more than 20MB.

var maxSize = 1048576 * 2;
var grAtt = new GlideRecord('sys_attachment');
grAtt.addQuery('size_bytes', '>',maxSize);
grAtt.query();
while (grAtt.next()) {
  gs.print(grAtt.file_name + ',' + grAtt.table_name + ',' + grAtt.size_bytes + ',' + grAtt.sys_id);
}

By Hitoshi Ozawa / Administering attachments

script to reported:

Num of records for ALL tables
Size in MB of ALL records
Size of Attachments Compressed / Uncompressed (Gbytes)

function doit(table) {

  var count = new GlideRecord(table);
  count.query();
  retval = count.getRowCount();
  if (retval > 0) gs.print(table+' : '+ retval);
  return retval;

}

var recordcount =0;
var sizeGb = Math.pow(2.0,30);
var sizeMb = Math.pow(2.0,20);

gr1 = new GlideRecord('sys_db_object');
gr1.addEncodedQuery('sys_scope=global^super_class!=NULL');
gr1.query();


while(gr1._next()){  // loop over each table
	recordcount += doit(gr1.name);
}


gs.print('TOTAL RECORDS ACROSS ALL TABLES :'+recordcount);
gs.print('APPROX SIZE OF RECORDS (MBytes): '+ Math.round(5090.0*recordcount/sizeMb,3));


// NOTE AVG SIZE OF RECORD IS 5090bytes  
// This average was calculated from extracting 1000 records (without attachments) from the 7 largest tables
// in XML format, adding the sizes of each extract and then dividing the total by 7000. 
// As such it is a reasonable, with XML ovehead but no index inofrmation, but tending on the HIGH side of an average value.


findAttachmentsSize();
function findAttachmentsSize(){
 var size_compressed = 0;
 var size_bytes = 0;
 grAttachment = new GlideRecord('sys_attachment');
//grAttachment.setLimit(100); //for testing small sample sets
 grAttachment.query();
 gs.print('Total Number of Attachments: ' + grAttachment.getRowCount());
 while (grAttachment.next()){
if(grAttachment.size_compressed > 0) {
 size_compressed = size_compressed + parseInt(grAttachment.size_compressed);
 size_bytes = size_bytes + parseInt(grAttachment.size_bytes);
 } }
var sizeGb = Math.pow(2,30);
 gs.print('Total Size Compressed (Gbytes): ' + Math.round(size_compressed/sizeGb,2));
 gs.print('Total Size Bytes (Gbytes): ' + Math.round(size_bytes/sizeGb,2));

}

by IanGlencross

===== Estimate the Size of a Database ===== By Microsoft

When you design a database, you may have to estimate how large the database will be when filled with data. Estimating the size of the database can help you determine the hardware configuration you will require to do the following:

Achieve the performance required by your applications.

Guarantee the appropriate physical amount of disk space required to store the data and indexes.

Estimating the size of a database can also help you determine whether the database design needs refining. For example, you may determine that the estimated size of the database is too large to implement in your organization and that more normalization is required. Conversely, the estimated size may be smaller than expected. This would allow you to denormalize the database to improve query performance.

To estimate the size of a database, estimate the size of each table individually and then add the values obtained. The size of a table depends on whether the table has indexes and, if they do, what type of indexes.

Calculate the Space Used to Store Data in the Leaf Level by MS

Specify the number of rows that will be present in the table:

Num_Rows = number of rows in the table
Specify the number of fixed-length and variable-length columns and calculate the space that is required for their storage:

Calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification.

Num_Cols = total number of columns (fixed-length and variable-length)

Fixed_Data_Size = total byte size of all fixed-length columns

Num_Variable_Cols = number of variable-length columns

Max_Var_Size = maximum byte size of all variable-length columns
If the clustered index is non-unique, account for the uniqueifier column:

The uniqueifier is a nullable, variable-length column. It will be non-null and 4 bytes in size in rows that have non-unique key values. This value is part of the index key and is required to make sure that every row has a unique key value.

Num_Cols = Num_Cols + 1

Num_Variable_Cols = Num_Variable_Cols + 1

Max_Var_Size = Max_Var_Size + 4

These modifications assume that all values will be non-unique.
Part of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:

Null_Bitmap = 2 + ((Num_Cols + 7) / 😎

Only the integer part of the previous expression should be used; discard any remainder.
Calculate the variable-length data size:

If there are variable-length columns in the table, determine how much space is used to store the columns within the row:

Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

The bytes added to Max_Var_Size are for tracking each variable column. This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a smaller percentage of the variable-length column storage space will be used, you can adjust the Max_Var_Size value by that percentage to yield a more accurate estimate of the overall table size.

Obs: This is a copy of MS SQL but however the dabatase calculation it is the same as MariaDB, MySQL, MS SQL or Oracle, but don't forget for a FULL calculation don't miss Index Information space.

summary

KB0780216 - Data Retention, Rotation, Cleanup and Instance Size

KB0723580 - Table Structures FAQ

KB0727158 - Static row size limit (65,535) can be exceeded on shadow tables

CMDB tables descriptions

Data migration process for archiving table data from non-reference fields to reference fields

Limiting conversation data growth

KB0521483 - Cannot increase the max_length of a string field on an Oracle database

Journal field display limits

MY SQL File Space Management

Participe, entre nas comunidades, acompanhem os posts: