Build 9493 Database Management

[support]

Problem:

When you upgrade from a build prior to Version 9493, your Passwordstate database will support Unicode characters, which can make your database grow by a large amount.  To ensure you have a seamless upgrade experience we'd recommend the following database management options:

 

Steps to Consider Before you Upgrade:

 

**Note** If you are using SQL Express, this has a 10 GB data limit. Your database should really no no bigger than 2GB if using SQL Express, otherwise you may have to consider Moving your database to a SQL Standard instance. 

 

Option #1: Set your Recovery model for your database to "Simple".  This will still allow full backups to be taken, but will significantly reduce the size of your database.  **Note** you should not set your database to Simple mode if you have any form of SQL Replication running.

Action: Connect to your database server using SQL Management Studio Tools, right click your Passwordstate database, and select Properties.  Now change the recovery model to Simple:

 

 

Option #2: Check you have plenty of disk space on your database server prior to upgrading, as your database may grow to be 300% – 400% in size.

Action: Connect to your database server using SQL Management Studio Tools, right click your Passwordstate database, and select Properties.  Select the Files Menu and determine which disk drive the database is stored on, and also determine the current size of your database. Check that there is ample space on this drive and free up space if required. 

 

 

 

Option #3: Shrink the database and log files to reduce the overall size of the database. 

Action: Connect to your database server using SQL Management Studio Tools, create a New Query and paste in the following code.  Now click the Execute button:

 

DBCC SHRINKDATABASE (Passwordstate)

 

 

 

Option #4: Confirm your Autogrowth setting is not limited.  If your database has a limit on how much it can grow, this may impact your upgrade.

Action: Connect to your database server using SQL Management Studio Tools, right click your Passwordstate database, and select Properties.  Select the Files Menu, and check the database is set to Autogrowth.  If it is not, click the Edit button and select Autogrowth and Unlimited as per below screenshot:

 

 

 

Option #5: A lot of customers see a build up of Passwordstate auditing data over the years, and this can consume quite a lot of space in the database.  

Action:  If you generate a lot of auditing records with your usage of Passwordstate, then most likely the majority of the records will be in the AuditingArchive table. To check the number of rows you can run the following queries in SQL Server Management Studio:

 

USE Passwordstate

SELECT COUNT(*) FROM AuditingArchive

 

With our testing, 1 or 2 million records do not pose an issue with the upgrade.  If you have significantly more records than this, please consider backing up and then deleting the records in the AuditingArchive table. Backing up the data first is only required for your own compliance reasons - The decision is yours to backup the data before deleting it.

 

To backup your AuditingArchive table, right click the Passwordstate database and select Export Data...

 

To delete all rows in the AuditingArchive table, run the following command in SQL Management Studio Tools:

 

USE Passwordstate

TRUNCATE TABLE AuditingArchive

 

 

 

Steps to Consider After you Upgrade:

 

The only step required after upgrading would be to Shrink the database again.  Follow Option #3 above to perform this step.

 

Regards,

Click Studios Support

[SmithAnderson]

Apologies for reviving this old thread, but I’d like to thank you for sharing these helpful tips for managing the database. It's great to see such detailed instructions to ensure a seamless upgrade experience. Keeping the size of the database in check is definitely important. On a side note, I wanted to share that in our business processes, we have recently started using the hr management system, and it has been a game-changer for us. This tool has proven to be incredibly effective in facilitating interaction between employees and has significantly sped up our work processes.

[support]

Thanks for your kind feedback - we really appreciate it.

 

Have a great day.

Regards

Click Studios