Maintenance

This topic is for advanced users

Software Maintenance

This topic is for under construction

HandPunch Maintenance

See HandPunch Maintenance

Database Maintenance

This topic is for under construction

As the database maintains an audit log of all changes made in the software, the database will grow to be very large in size after a few years. It is not practical to keep the contents of the audit table as this can account for over 50% of the size of your database.

The following SQL script will show you the number of rows in each of your database tables:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so 
INNER JOIN 
    sysindexes si 
ON 
    si.id = OBJECT_ID(so.name) 
WHERE 
    OBJECTPROPERTY(so.id, 'IsMsShipped') = 0 
    AND so.xtype = 'U' 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC;

The results of this query are:

Audit                  9472425
Schedule	       2098028
Punch	                844036
RawPunch	        843681
Hours	                561563
Exception	        516282
EmployeeHistory           4788
SavedReportSelection	  3552
Comment                   2400
Assignment	          1942
EmployeeStatus	          1405
EmployeeShift	          1080
EmployeeClockID            911
EmployeeExtended	   850
Employee	           849
EmployeeEmployment	   638
PayPeriod	           394
OvertimePeriod	           384
EmployeeHealth	           336
EmployeeLicensing	   336 .....

The audit table in this example of a single facility database used for 6 years. The database is ~7 Gb in size.

The fastest method of getting data out of the database is by using the bcp command. An example of this is:

bcp "SELECT * from Ta..Audit WHERE DatabaseDateTime >= '01/01/2015' AND DatabaseDateTime < '01/01/2016'" queryout audit2015.txt -c -S SERVER\INSTANCE -U user -P password -o audit2015.log

By using a similar script for each year that the database has been in use, you will create a text file for each year that contains the contents of the audit table. We recommend that you then zip up each year so that you do not accidentially overwrite these files on subsequent purges. The zip will contain both the log and the data for reference. A 100 Mb text file represents ~ 800,000 rows of the Audit table and when zipped will be ~10% of the size. For example, one million audit records will end up as a ~15Mb zip file.

The file from the current calendar year should not be zipped up. This is going to be reloaded back into the Audit table once the table is completely purged.

The following script will drop and recreate the Audit table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit]
GO

CREATE TABLE [dbo].[Audit] (
	[PkAuditID] [bigint] NOT NULL ,
	[FkLoggedInID] [bigint] NULL ,
	[FkEmployeeID] [bigint] NULL ,
	[Object] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FkObjectID] [bigint] NULL ,
	[Method] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Value] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PreviousValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ComputerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ComputerLoginName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ComputerDateTime] [datetime] NULL ,
	[DatabaseDateTime] [datetime] NULL ,
	[FkCreatedByID] [bigint] NULL ,
	[CreatedDateTime] [datetime] NULL ,
	[FkUpdatedByID] [bigint] NULL ,
	[UpdatedDateTime] [datetime] NULL ,
	[FkDeletedByID] [bigint] NULL ,
	[DeletedDateTime] [datetime] NULL ,
	[Deleted] [bit] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Audit] WITH NOCHECK ADD 
	CONSTRAINT [PK_Audit] PRIMARY KEY  CLUSTERED 
	(
		[PkAuditID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

CREATE  INDEX [IX_FkLoggedInID] ON [dbo].[Audit]([FkLoggedInID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_FkEmployeeID] ON [dbo].[Audit]([FkEmployeeID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_Object] ON [dbo].[Audit]([Object]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_FkObjectID] ON [dbo].[Audit]([FkObjectID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_Method] ON [dbo].[Audit]([Method]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_DatabaseDateTime] ON [dbo].[Audit]([DatabaseDateTime]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_PkAuditID_Object] ON [dbo].[Audit]([PkAuditID], [Object]) ON [PRIMARY]
GO

The Audit table and now empty and the current year's file is now loaded back.

bcp Ta..Audit in audit2015.txt -c -S SERVER\INSTANCE -U user -P password -o loadaudit2015.log

We recommend that you confirm the number of rows in the log file create when dumping the current year's data with the number of rows loaded back into the database.

This is what shows in the log file:

385877 rows copied.

Here is a query that gives us the total number of rows in the Audit table:

SELECT COUNT(*) FROM Audit

Upon seeing that the results are identical you can continue.

Shrinking this database reduced the size from ~7Gb to less than 3Gb.