IRIS SQL Server Database Maintenance

How to check the size of the IRIS SQL database and to display data and log space information

Simple Recovery Model

Log File Size

Monitor Database and Log Space Use

Autoshrink

What is a Compatibility Level

SQL Server Memory

Configure the locks option

 

 

How to determine the version of SQL Server that is installed

  1. Open SQL Server Management Studio and right-click on the instance name and select Properties. The Product row indicates your version.

  2. Microsoft SQL Server Express is only recommended for installations of up to six users.

 

How to check the size of the IRIS SQL database and to display data and log space information

  1. Connect to an instance of SQL Server and then expand that instance.

  2. Expand Databases.

  3. Right-click the database and select Reports | Standard Reports, and then click Disk Usage.

  4. Express versions of SQL Server have a database size limit of 10GB.

 

 

Simple Recovery Model

IRIS uses the Simple Recovery Model.  Recovery models are designed to control transaction log maintenance.

  1. From the properties page select Options | Recovery Model | Simple

 

 

Log File Size

If the transaction log grows too larger it can be shrunk.

  1. Right-click on the database | Tasks | Shrink | Files

  2. Change the file type from Data to Log.

  3. Click OK (with Release unused space option selected).

 

 

Monitor Database & Log Space Use

  1. Right-click Database | Properties

  2. Select Files.

  3. Scroll across to the autogrowth column (right-hand side).

  4. Next to log file select the Browse icon.

  5. Change options to 50MB, Unlimited and 10% Unlimited.

 

 

Autoshrink

  1. From the Properties page select Options.

  2. Make sure the Auto Shrink option is set to False.

 

 

What is a Compatibility Level

The main reason for having compatibility levels is to allow for Backward Compatibility that is, if you previously had an older SQL Server 2008 database and restored it on a new SQL Server 2012 instance.

To see the compatibility level of each database:

  1. Right-click the database in SQL Server Management Studio and select Properties, then select the Options tab.

Further Reading: https://www.spiria.com/en/blog/web-applications/understanding-sql-server-compatibility-lev

 

 

 

SQL Server Memory

  1. In Object Explorer, right-click a server and select Properties.

  2. Click the Memory node.

  3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB).

 

 

Configure the locks option

  1. In Object Explorer, right-click a server and select Properties.

  2. Click the Advanced node.

  3. Under Parallelism, type the desired value for the locks option.

Use the locks option to set the maximum number of available locks, thereby limiting the amount of memory SQL Server uses for them. The default setting for locks is 0.