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
- Open the SQL instance using SQL Server Management Studio.
- Expand Databases, expand the database that contains the table with the specified indexes, and then expand Tables.
- Expand the table in which the indexes belong.
- Right-click Indexes and then click Rebuild All.
- To start the rebuild operation, click OK.
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;
GOEXEC 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 click 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.
This cleans up the Payroll module, i.e. deletes data from CurrentAllowance, CurrentDeduction and InputAllowance, InputDeduction, InputProportionalCosting and TransCurrentMaster.
- Go to Payroll | Purge Closed Pays command
Select the pays to be purged and press OK
Confirm you want to purge
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
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.