sqlorcl

Just another WordPress.com site

Audit Log Trimming Job in Share point run in a multiple batch

Audit Log Trimming Job in Share Point 2010 tracks all activities that occur within the environment. The site collection administrator has the ability to set auditing requirements within the environment that determine the types of actions that should be tracked.
But one of the Share Point Server audit is configured and the audit purge is not scheduled. Audit Log Trimming Job was not running for more than a year so the AuditData table has grown to 65 GB in size, as well as more than 2 billions of record.
One of the Share Point admin scheduled this job to run every day during the low activities, in the database server. The job kicks out for the very first time and continues running for 12 hours but not completed. This job was deleting data from AuditData table. As a result, a large number of locks created to the database server and users were not able to access some functionality and sites in Share Point.
Due to SLA agreement for minimum downtime during business hours, we had to kill the session and all transactions were rolled back. This also took a considerable time to rollback. Later on we decided to purge the data manually instead of running the job and keeping the data up to one month old in the Audit table. Once we manually delete data from table, we rescheduled the job again and that time the job ran successfully in few minutes. Here is the procedure we followed to purge the AuditData Table.

Step 1:
— Take a full backup of the Database.

USE [master]
GO

BACKUP DATABASE [xxx] TO DISK = N’F:\xxxxxxx.BAK’ WITH NOFORMAT, NOINIT, NAME = N’xxxx-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 2:

Find out which site in SharePoint had most numbers of data in AuditData table by using following query.

WITH SiteLogin (tp_SiteID,tp_ID,tp_SiteAdmin,tp_Login,RowNumber) AS
(
select tp_SiteID,tp_ID,tp_SiteAdmin,tp_Login
,ROW_NUMBER() over(partition by tp_SiteID order by tp_SiteID) RowNumber
from dbo.UserInfo
where tp_SiteAdmin = 1
)

select FullUrl,a.SiteId,COUNT(a.SiteId) as total , Max(a.Occurred) as MaxDate,
MIN(a.Occurred) as MinDate,tp_ID
from AuditData a inner join Sites s on a.SiteId = s.Id
inner join SiteLogin l on a.SiteId = l.tp_SiteID
where l.RowNumber = 1
group by FullUrl,SiteId,tp_ID
order by FullUrl

We saved this query result in excel file. Later we used this excel file to get the parameter values for the store procedure we used in the steps.

Table has more than 2 billions of record and database was full recovery mode and we didn’t have enough free disk space in the log drive. So we had to delete the rows in a batch by selecting the occurred date in AuditData table. In our case, we found that the 95% rows in the table belonged to one site. So we deleted the rows for that site in a batch and other sites in one statement.

Step 3:

Disable all non-cluster indexes in AuditData table.
This improved the delete operation time.

USE [xxxxxx]
GO

alter index AuditData_OnItem on dbo.AuditData disable
GO

Step 4.a:
— In this step we created a temp table.
— This table stored the key values (which were Siteld and Occurred columns) from AuditData Table. We filtered the key values based on occurred column data in AuditData table. So the temp table stored only the key values for that particular batch.
— Later delete query joined temp table with AuditData table on key values to delete the rows.
— Joining with temp table to the base table during large number of data delete would create less numbers of Key level locks in the database.

USE [xxxxx]
GO

declare @SiteId uniqueidentifier
declare @EndDate datetime
declare @count int
declare @UserId int

set @SiteId = —- From Step 2 excel file
set @EndDate = ‘xxxxxxxxx’
set @UserId = — From Step 2 excel file.

@EndData values would be between MaxData and MinData in excel file to the corresponding SiteId. We decided one month as our batch interval. That might be different to your environment depending how the data are speared in the table based on occurred data column for that particular site.

CREATE TABLE #AuditDataDelete
(
[SiteId] [uniqueidentifier] NOT NULL,
[Occurred] [datetime] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE #AuditDataDelete ADD PRIMARY KEY CLUSTERED ([SiteId],[Occurred])
GO

Insert Into @AuditDataDelete
Select [SiteId],[Occurred]
From AuditData with(nolock)
Where [SiteId] = @SiteId and [Occurred] < @EndDate

Delete AD
From AuditData AD
Inner Join AuditDataDelete TD
On TD.[SiteId] = AD.[SiteId] and TD.[Occurred] = AD.[Occurred]

SET @count = @@ROWCOUNT

Step 4.b:
— In this step we recorded in the AuditData table about the previous step delete statistics.
— In order to record the statistics we had to run proc_AddSiteAuditEntryFromSql store procedure.
— This store procedure has a few parameters and here were the values for the parameters we applied
UserID:from Step 2 excel file.
SiteId :from Step 2 excel file.
EventID: 50 default.

— Followed the steps to find the AuditDataEvenparameter Value.

@count – previous step
@EndDate – Previous Step.

DECLARE @AuditEventData nvarchar(4000)
DECLARE @Ret int
SET @Ret = 0

SET @AuditEventData = STUFF(STUFF(”, 3, 0, ‘DeleteEntriesInfo’), 1, 0, STUFF(STUFF(STUFF(”, 3, 0, ‘Rows’), 1, 0, STUFF(@count, 1, 0, STUFF(”, 2, 0, ‘Rows’))) + STUFF(STUFF(”, 3, 0, ‘EndDate’), 1, 0, STUFF(@EndDate, 1, 0, STUFF(”, 2, 0, ‘EndDate’))), 1, 0, STUFF(”, 2, 0, ‘DeleteEntriesInfo’)))

EXEC @Ret = proc_AddSiteAuditEntryFromSql
@SiteId,
@UserId,
50,
@AuditEventData

Step 4.c:
–Database was in Full recovery mode, so after each batch we had to backup transaction log. Otherwise the log file would grow very large and we might experience run out of disk space error.
— Time stamp also added to each Log backup file so previous log file wouldn’t be not overwritten.
— This way if we need to require restored we could do to a point of time.
— Log file also shrunk in this step in order to free up VLF.

DECLARE @sql nvarchar(4000)
DECLARE @filename nvarchar(255)
DECLARE @folder nvarchar(255)
DECLARE @full_path_and_filename nvarchar(1000)
DECLARE @err_msg nvarchar(2000)
DECLARE @crlf varchar(2)
SET @crlf = CHAR (13) + CHAR (10)

SET @folder = ‘X:\XXXXX\’
SET @filename = ‘XXXXX’ + ‘_backup_’ + REPLACE (REPLACE (REPLACE (CONVERT (char (16), CURRENT_TIMESTAMP, 120), ‘-‘, ”), ‘ ‘, ”), ‘:’, ”)
SET @full_path_and_filename = @folder + @filename + ‘.TRN’

SET @sql = ‘BACKUP ‘ +’LOG’ + ‘ ‘ + QUOTENAME (‘VIP_Sites’) + @crlf
SET @sql = @sql + ‘TO DISK = ‘ + QUOTENAME (@full_path_and_filename,””) + @crlf
SET @sql = @sql + ‘WITH’ + @crlf
SET @sql = @sql + ‘ NOFORMAT,’ + @crlf
SET @sql = @sql + ‘ NOINIT,’ + @crlf
SET @sql = @sql + ‘ NAME = ‘ + QUOTENAME (@filename,””) + ‘,’ + @crlf
SET @sql = @sql + ‘ SKIP, NOREWIND, NOUNLOAD, STATS = 10 ‘ + ‘,’ + @crlf
SET @sql = LEFT (@sql, LEN (@sql) – 3)

–PRINT @sql
EXEC (@sql)
GO

DBCC SHRINKFILE (XXXXXXXX,1)
GO

Step 5:
We repeated the step 4 in a multiple batch depending on Site’s data in AuditData table. The goal was to keep all sites audit data only for one month. In our case, if any site had more the 9 millions of record in AuditData table, we considered multiple batch delete for this site. Otherwise we deleted this site’s data in a single batch. Besides the decision whether to consider multiple or single batch; deleting will depend on how much free space is in your log drive. So transaction log file can grow, as well as transaction log backup interval and your downtime of the application.

Step 6:
— The table might have internal fragmentation after this large delete operation. So we ran this DBCC command to recover the empty space from the table and this command will recover space from a table ifthat table has any variable column present.

USE [xxxxx]
GO
DBCC CLEANTABLE (‘xxxx’, ‘AuditData’)
GO

Step 7:
— Rebuild the Cluster Index

USE [xxxxxx]
GO

ALTER INDEX AuditData_OnSiteOccurred ON dbo.AuditData REBUILD;
GO

Step 8:
— Rebuild all non-cluster indexes.

USE [xxxxxx]
GO

ALTER INDEX AuditData_OnItem ON dbo.AuditData REBUILD;
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: