Optimizing and freeing up space SQL Databases

Official translation unavailable. Translate this page with Google:

Problem

Using Control Center with Microsoft SQL Server Express databases can cause problems until you remove old print logs to free limited space. Your Document Management System in Control Center stores your data on your Microsoft SQL Server (default database: NiceAN). You get errors when your database is full and can’t save more data. Newer versions of Microsoft SQL Server Express have database size limits of 10 GB. When you disable data retention, NiceAN fills with data and causes problems when full.

Label printing data uses minimal space on NiceAN:

  • Labels

  • Images

  • Configuration files

Print logs use most of your space on NiceAN:

  • Timestamps

  • Users printing

  • Labels printing

  • Printers used

  • Actual data you print

Solution

To free space, use [nan].[RemovePrintJobsHistoryData] in NiceAN to automatically remove old print logs you no longer need.

  • For NiceLabel 2019.3 and newer, use steps 4-6.

  • For versions 2017.2 to 2019.2, use steps 1-6.

  • Version older than 2017.2 are not supported.

Do the following:

1. Download and extract the ZIP file on your computer

2. Run the SQL script for your server (default database: NiceAN)

3. Make sure [nan].[RemovePrintJobsHistoryData] is in NiceAN > Programmability > Stored Procedures

4. Set your NiceAN Recovery model to Simple (logs minimal data):

Note

This limits your current transaction log recording. To restore full transaction logging later, restore your Recovery model to Full.

4.1 Go to Microsoft SQL Server Management Studio 4.2 Right-click NiceAN

4.3 Select Properties

4.4 Go to Database Properties > Options

4.5 For Recovery model, select Simple

4.6 Click OK

5. Run [nan].[RemovePrintJobsHistoryData] with your settings. Example:

EXEC [nan].[RemovePrintJobsHistoryData] '2020-01-20', 100
  • DateTo: deletes all print logs from NiceAN prior to 2020-01-20 (YYYY-MM-DD)

  • BatchSize (optional): removes print logs in batches (100 by default)

6. Optimize your data removal process (optional):

  • Use DateTo to delete larger chunks of your print logs (for months or even years).

  • Increase your BatchSize parameter to increase the amount of data deleted at once.

  • If you get errors, lower your BatchSize (your server is probably full so your print logs cannot delete).

  • After you delete your old print logs, reduce your transaction log size with DBCC SHRINKFILE. For more information, read DBCC SHRINKFILE (Transact-SQL)

  • Disable data retention in Control Center > Administration > Archiving to avoid running out of space again

Managing and optimizing limited space in your SQL databases helps you avoid future errors, decreases your production downtime, and saves you time spent maintaining your label printing environment.