Shrinking Database Size

Problem

GlobalSearch versions 5.0.0.0 and higher as well as GlobalCapture versions 2.2 and higher are shipped without Microsoft SQL Server and some users may opt to utilize Microsoft SQL Server Express. While this may be sufficient for most users, there are some limitations that you should be aware of. In addition to limiting the number of CPUs that can be used, SQL Express limits the amount of RAM that can be consumed by the SQL engine as well as limiting the individual database size to a maximum capacity. Please see the chart below for information regarding the limitations of SQL Express.

Please note that GlobalSearch versions lower than 5.0.0.0 and GlobalCapture versions lower than 2.2 were shipped with Microsoft SQL Server Express by default.



SQL Version

CPU

RAM

Database Size

SQL Version

CPU

RAM

Database Size

2012

1 CPU

1GB

10GB

2014

1 CPU

1GB

10GB

2016

1 CPU

1GB

10GB

2017

1 CPU

1GB

10GB

2019

1 CPU

1GB

10GB



Once the database has reached the limit, SQL Server will no longer allow any data to be stored in that database which will prevent Square 9 software from working properly.

Solution

There are three options to resolve a database reaching its maximum size.  Depending on the situation, some or none of these may be appropriate for you.

Purchase SQL Standard

Purchasing Microsoft SQL Standard is the quickest and most painless way to recover from a database that has reached its maximum capacity.  Microsoft SQL Server Standard edition will remove all limitations on database size (up to 524 PB) as well as raise the maximum resources that may be consumed by the SQL Engine.  To purchase Microsoft SQL Server please visit https://www.microsoft.com/en-us/sql-server/

Create a New Database

Depending on the situation you may be able to create a new database to continue adding data.  If you have run out of space in your GlobalSearch database you can create a new database from within the software (license restrictions apply).  For GlobalForms or GlobalCapture you may create a new database within SQL Management Studio and repoint the software to the new database.  Please note: creating a new database will make your data within the application scattered as each database is completely separate from the other.  This means your data may be split between two different places and may be hard to work with.

Purging Data and Shrinking the Database

Microsoft SQL Server has a built-in command that allows the database size to be compressed.  If you have old data within your database, purging this data and then performing the shrink commands will allow you to continue using your existing database without making any additional configuration changes.  If you have old data that you no longer need, delete it and then have a qualified IT Professional perform commands on the databases.  To find out more about database shrinking please see Microsoft's article on how to Shrink a Database

Warning

You should always take a backup before performing any database operations. Failure to take a proper backup may result in permanent data loss.



Related articles