Stage: /* Database Maintenance */

2015-05-17T17:16:13Z

‎Database Maintenance

Stage: Created page with "{{Advanced}} == Software Maintenance == {{Under Construction}} == HandPunch Maintenance == See HandPunch Maintenance == Database Maintenance == {{Under Construction}..."

2015-05-17T16:52:23Z

Created page with "{{Advanced}} == Software Maintenance == {{Under Construction}} == HandPunch Maintenance == See HandPunch Maintenance == Database Maintenance == {{Under Construction}..."

New page

{{Advanced}}

== Software Maintenance ==

{{Under Construction}}

== HandPunch Maintenance ==

See [[HandPunch Maintenance]]

== Database Maintenance ==

{{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:

<nowiki>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;</nowiki>

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 ~ 1,000,000 rows of the Audit table.

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:

<nowiki>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</nowiki>

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.

@@ Line 63: / Line 63: @@
   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 ~ 1,000,000 rows of the Audit table.
+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.

Maintenance - Revision history

Stage: /* Database Maintenance */

Stage: Created page with "{{Advanced}} == Software Maintenance == {{Under Construction}} == HandPunch Maintenance == See HandPunch Maintenance == Database Maintenance == {{Under Construction}..."