Skip to main content
Skip table of contents

Maintaining the SQL database

This document is intended for SQL database administrators in conjunction with Payroll Administration staff. This document explains how to manage the size of your SQL database without removing essential information.

Your SQL database will grow continuously because PayGlobal processes occur every day. You cannot stop this growth, but you can manage it.

Database information relates to terminated and current employees, so you could archive portions of your database. However, archiving has the following problems:

  • Requires many SQL scripts to move the data to a new database.
  • Runs the risk of losing referential integrity.
  • Creates an additional database that increases in size.

Archiving is not a suitable maintenance solution because it does not solve the underlying problem of database growth. The recommended solution in this manual minimises areas of the database that retain non-essential information.

IMPORTANT: Before undertaking ANY maintenance on your database, ensure you take a backup of the both the database and PayGlobal Data folder. This will safeguard your organisation for auditing and/or troubleshooting requirements.

Regular maintenance

The frequency that you run the tasks described in this section depends on:

  • The number of audit logs that your PayGlobal database generates.
  • Your disk space availability.
  • Your organisational preferences.

Regular maintenance procedures will help you optimise the performance of your database.

However, each database is unique so your consultant may advise you run alternative or additional maintenance procedures or run these tasks on an alternative time scale.

Daily

Backup your database

Daily backups safeguard the loss of any:

  • Data entry completed that day
  • Linkage to in-flight tax reporting submissions

Weekly

Clean-up Local Folders

When you run a report, if you have configured the report to go just to the PayGlobal Inbox, then this impacts the size of the Messages table. Without regular maintenance this will affect the size of your database – growing need for more disk space. It also could potentially impact the performance of the database.

The use of Local folders was designed to be used as a temporary location for accessing reports that have been created. Important reports that are required to be held for a given period should be moved out of the database and into a dedicated document management environment.

It is recommended that reports only be configured to go to the Inbox as a backup if sending to Email/File/FTP fails.

Manually deleting from the Inbox or Send Items just moves these records to the Local Folders - Deleted Items, which means complete deletion via the UI is a 2-step process.

It is recommended to ensure Send and Receive is run to distribute items from the Outbox before deleting data from the Message table via SQL script.

Running Send and Receive can be automated using PayGlobal Scheduler.

Reindexing data

Regularly performing a Reindex keeps your database in optimum condition by removing deleted records and compacting the remaining records – which includes removal of empty space.

This process reduces the size of the data folder and backup.

Whilst this action can be performed within the PayGlobal UI using the “Reindex and Pack” function that feature can be very slow on large database

Reindex on a schedule – via SQL

The easiest option long term is to setup index maintenance on a schedule so that it runs at least once a week at a time PayGlobal will not be in use.

SQL Server has a Maintenance Plan feature which allows you to setup index maintenance tasks on a schedule. We recommend choosing the Rebuild Index, Reorganize Index, and Update Statistics tasks in that order if you go this route. Using the default Microsoft fragmentation and page count limitations on when to run these tasks is recommended.

Another option is to setup a custom index maintenance script on a schedule. An example of a custom script is included at the end of the next section.

Reindex Manually – via SQL

You need sole access to PayGlobal before you reindex in order to prevent data corruption.
This can be achieved by applying a Session Lock (Administration | Maintenance | Lock System command).
http://msdn.microsoft.com/en-us/library/ms189858.aspx

To rebuild an index
  1. Open the SQL instance using SQL Server Management Studio.
  2. Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.
  3. Expand the table in which the index belongs and then expand Indexes.
  4. Right-click the index to rebuild and then click Rebuild.
  5. To start the rebuild operation, click OK.
To rebuild all indexes on a table
  1. Open the SQL instance using SQL Server Management Studio.
  2. Expand Databases, expand the database that contains the table with the specified indexes, and then expand Tables.
  3. Expand the table in which the indexes belong.
  4. Right-click Indexes and then click Rebuild All.
  5. To start the rebuild operation, click OK.
  6. Once you have rebuilt the indexes above, please run the following SQL script over the database.

    It can take up to 60 minutes to run on large databases. But you can run it while users are logged in and using the system.

    SET NOCOUNT ON;
    DECLARE @CurrentDB NVARCHAR(200)
    SELECT @CurrentDB = DB_Name() DECLARE @objectid int; DECLARE @indexid int;
    DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float;
    DECLARE @command nvarchar(4000);
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
    -- and convert object and index IDs to names. SELECT
    object_id AS objectid, index_id AS indexid,
    partition_number AS partitionnum, avg_fragmentation_in_percent AS frag
    INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(@CurrentDB), NULL, NULL , NULL, 'DETAILED')
    WHERE avg_fragmentation_in_percent > 0 AND index_id > 0 order by avg_fragmentation_in_percent desc;
    -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
    -- Open the cursor. OPEN partitions;
    -- Loop through the partitions. WHILE (1=1)
    BEGIN;
    FETCH NEXT
    FROM partitions
    INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK;
    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid;
    SELECT @indexname = QUOTENAME(name) FROM sys.indexes
    WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*)
    FROM sys.partitions
    WHERE object_id = @objectid AND index_id = @indexid;
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
    IF @partitioncount > 1
    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command);
    PRINT STR(@frag, 25, 5) + N' - Executed: ' + @command;
    END;
    -- Close and deallocate the cursor. CLOSE partitions;
    DEALLOCATE partitions;
    -- Drop the temporary table. DROP TABLE #work_to_do;
    GO
    EXEC sp_updatestats GO

Quarterly

The frequency that you run these tasks depends on:

  • The number of audit logs that your PayGlobal database generates.
  • How much hard disk space you have available.
  • Your organisational preferences.

These tasks can be run via SQL scripts or via PayGlobal UI. This section of the document describes

Maintain Log Files

Every time PayGlobal runs a process, for example, when you run a command such as Process Pay, an Audit Log file is generated. The Maintain Log Files entity lists the current log files in your database. These are also found in the Log folder of your PayGlobal data directory.

As log files accumulate, they impact the available disk space on your system. You need to periodically clear old log files from your system. If you are using the Maintain Log files entity to do this, then as a safety measure, PayGlobal will only allow you to delete audit logs that are more than five days old.

When you run the Maintain Log Files command, PayGlobal opens the Log File Maintenance form. In here you can change the order of the logs displayed by clicking the column header.

  • To delete just one record, click the Delete box on row you want to delete then click the Delete Tagged button.
  • To select multiple consecutive items, click the first record in the group then hold the shift key down and click the last record in the group, then click the Delete Tagged button.
  • To select all items, click on any row then press CTRL + SPACE, then client the Delete Tagged button.

Maintain Backups

Old backup files need to be regularly deleted to free up disk space.

It is recommended to keep a copy of the backup done on completion of the last pay in a tax year AND the most recent.

The database administrator should delete a database backup using a database management application. You should only delete a database backup from within PayGlobal if you do not use a database management application.

Annually

Purging old/non-essential tables

Data is responsible for more than 90% of the database size. You can significantly reduce the size of your database by regularly purging data that you no longer use or need.

There are multiple ways data can be purged from the database:

  • Purge closed pays
  • Manage Tables – Purge function
  • Manage Tables – Clear tables function
  • SQL scripts

Be mindful of the legislative requirements in respect of record keeping.

Typically, legislation states you must keep payroll records for seven years.

Unfortunately, this can be complicated by more specific payroll compliance requirements the influence rates of pay for backpays, leave or termination payments.

The following sections explain each tool that can be used to help remove non-essential data.

Purge Closed Pays

PayGlobal is designed to use data from “Current” tables to complete actions like Process Pay, Process Actuals, Process Roster and produce some reports that are typically run prior to closing a Pay, Actual Pay or Roster Pay or immediately thereafter.

When a pay is closed, a copy of the pay data is added and saved to the tables prefixed with “Historical”. This effectively creates a duplication of the data. And, duplication can impact the integrity, performance and of the database. Therefore, it is recommended that closed pays are regularly purged from the “Current” tables.

This task could be done quarterly or 6-monthly on databases with high volumes of pays – especially those with high volumes of pay periods

Limitation: The Pay Period Calculation Listing, Employee Payslip Report and some other transaction reports (those prefixed PGPRTRAN) are designed to use data from the “Current” transaction tables only, i.e. purged pays do not show in the pay sequence selection list.
Copies of these reports should always be saved to a dedicated document management environment to prevent the need for re-runs.

To purge closed pays

This cleans up the Payroll module, i.e. deletes data from CurrentAllowance, CurrentDeduction and InputAllowance, InputDeduction, InputProportionalCosting and TransCurrentMaster.

  1. Go to Payroll | Purge Closed Pays command
  2. Select the pays to be purged and press OK

  3. Confirm you want to purge

  4. View the Audit log to ensure no errors are present.

    If there are any errors and you’re not sure how to handle them, contact Support as soon as possible. Leaving errors unresolved in this area could impact future pays.

Purge Closed Actuals

This cleans up the T&A module, i.e. deletes data from CurrentActualAllowance, CurrentActualTimeband and TransActualCurrentMaster.

The process is the same as for Payroll but instead go to Time & Attendance | Purge Closed Actuals command

Purge Closed Rosters

This cleans up the Roster module, i.e. deletes data from CurrentAvailability, CurrentRosterAllowance, CurrentRosterTimeband and TransRosterCurrentMaster.

The process is the same as for Payroll but instead go to Rostering | Purge Closed Rosters command

SQL Scripts

This section assumes the user knows how to use SQL Management Studio to open/write/run a “Query” but does not necessarily understand the PayGlobal schema.

Using SQL to change/remove data provides little auditability. BEFORE running ANY script that changes the data/table configurations, it is recommended that a CSV file of what will be deleted be created and saved to an appropriate location

Document Table

Any documents that will be accessed via PayGlobal Self Service or any documents that need to be accessed via the Documents tab on a record within PayGlobal are stored in the Documents table.

The most common document held in the Documents table are Payslips. These can take up a lot of disk space. We recommend holding only the last 3 months payslips in the database and suggest employees be encouraged to download a copy of the payslip and store somewhere externally (e.g. home computer).

If you are following the recommended practices in this document, then a payslip that’s up to a year old can be regenerated from PayGlobal or you should be able to get this from your external reporting archive.

As for other documents – especially those with employee sensitive information, you should ensure your organization has appropriate practices in place that enable you to identify those records that no longer need to be kept.

New Zealand

The law states personnel files must be kept for at least six years and pay records seven years. Outside of this, MBIE advise that once the employer no longer needs the information it should not be kept. MBIE’s recommendation is to securely destroy the information about six months after employment has ended. Refer


Australia

The law states personnel files and pay records must be kept for seven years. Records pertaining to Long Service are state specific. Refer:

Run the following SQL script:

SELECT * FROM Document
WHERE CreationDate < ‘YYYY-MM-DD’
AND DOCFOLDERID = ‘n’
  • ‘YYYY-MM-DD’ is the last date that you want to keep.
  • 'n’ is the Payslip folder for Self Service (such as 2).

Save and review results, if ok to delete those record run the following Delete script, else alter to reflect your organisation needs:

DELETE Document
WHERE CreationDate < ‘YYYY-MM-DD’
AND DOCFOLDERID = ‘n’
Message Table

Clearing the Outbox should be done within the PayGlobal UI by running the Send and Receive command. If items remain the Outbox, check the Send and Receive audit log to see why.

Ensure all critical documents are saved to an external location before deleting.

Run the following script:

SELECT * From Message WHERE FolderID in (0,2,3)
AND ModifiedDate < GETDATE() – n (or ModifiedDate < 'YYYY-MM-DD')
Order by FolderID, ModifiedDate

For date range, taylor the script to look back ‘n’ days or put in a specific date ‘YYYY-MM-DD’ of the last message that you want to keep.

The FolderID refers to which folder in Local Folders, i.e.

  • ‘0’ is the Inbox.
  • ‘1’ is the Outbox.
  • ‘2’ is Sent.
  • ‘3’ is Deleted

Save and review results, if ok to delete those records substitute the SELECT with DELETE, else alter the script further to reflect your organisation needs:

DELETE Message
WHERE FolderID in (0,2,3)
AND ModifiedDate < GETDATE() – n (or ModifiedDate < 'YYYY-MM-DD')

Other maintenance

To tailor a more concise maintenance plan for your organisation you may need a professional DBA. Please contact your PayGlobal Account Manager/Solution Manager to further guidance.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.