Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Designing for Performance and Integrity but have a Tran Problem Expand / Collapse
Author
Message
Posted Monday, December 2, 2013 1:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 233, Visits: 923
I have a DB that grows like crazy. This was a design decision I had nothing to do with, but now I have to manage the space this thing takes up. I need to archive the records from that DB, and have business rules to do so, anything over 90 days old can be archived.
I created a script that goes like this:

Simple description, without all bells and whistles:
While
Try
Select records from Audit into Temp table
Insert records from Temp table into Archive table
Delete records from Audit table
Drop temp table
End Try
Catch
@Error = ltrim(str(error_number()))
If error = 0
Log to a table how many records we moved.
Commit Tran
Continue
Else
Rollback Tran
Break from loop.
End Catch
Outside of loop, Send email if there is a problem, @error <> 0.

In development, running interactively it runs beautifully, and logs what it's doing just fine. When I run it in a job it works differently. In a job it does not log what it's doing to the table as it moves records, and it bunches all of the batched into on transaction, or multiple nested transactions. I suspect the nested transactions because I see the rollback slowly putting the records back into the prod table at the same batch size as they were taken out. This script should only roll back the batch it is in the middle of when the script errored or was killed.

Anyway, I'm trying to figure out what I am doing wrong here while. My goal was performance and data integrity without imposing on the app that's inserting all of the records I have to archive, such as by using With(NoLock).

Here is the full code:
Set NOCOUNT ON
declare @enddate datetime, @NumRecordsToGet int, @FirstDayOfQuarter datetime
Declare @NumRecords Int, @NumAffected Int
Declare @Err Int, @ErrMsg VarChar(255), @Body1 VarChar(500), @Email VarChar(2000)

select @enddate = convert(datetime,convert(varchar,dateadd(month,-3,getdate()),101)), @FirstDayOfQuarter = DATEADD(qq, DATEDIFF(qq,0,GetDate()), 0)

If datediff(day,@FirstDayOfQuarter,GetDate()) > 5 --don't archive for the first 5 days of each quarter; in case something goes wrong with the backup
Begin
While (select logtimestamp from ACSLog.dbo.auditlog where auditlogid = (select min(auditlogid) from ACSLog.dbo.auditlog)) < @EndDate
Begin
Begin Try
If Object_ID('TempDB..#archive') > 0
Drop Table #archive
Select Top 1 @NumRecordsToGet = NumAuditRecords, @Email = ErrorEmail from ACSLog_Archive.dbo.PurgeLimits
Begin Tran
Select Top (@NumRecordsToGet) * into #Archive from ACSLog.dbo.auditlog With (NoLock) Where logtimestamp < @enddate --Populate Temp table.
Insert Into ACSLog_Archive.dbo.AuditLog --Put records into archive table where they do not exist in archive table.
Select A.* from #Archive A
Left Join ACSLog_Archive.dbo.AuditLog B On A.AuditLogID = B.AuditLogID
Where B.AuditLogID Is Null
Delete A
From ACSLog.dbo.AuditLog A With (Rowlock)
Inner Join ACSLog_Archive.dbo.AuditLog B On A.AuditLogID = B.AuditLogID
Drop table #Archive;
End Try
BEGIN CATCH
Set @Err = ltrim(str(error_number()))
Set @ErrMsg = error_message()
--PRINT 'ErrNo: ' + @Err
--PRINT 'ErrMsg: ' + @ErrMsg
If @ERR = 0 --Check for errors and if none, Commit the transaction.
Begin
Insert Into ACSLog_Archive.dbo.PURGE_StatusLog (ArchivedCount, TableName)
Values (@NumAffected, 'AuditLog');
Commit Tran
Continue
End
Else
Begin
RollBack Tran
Break --out of while loop.
End
END CATCH
End
If @Err > 0
Begin
Set @Body1 = Convert(NVarChar(10), @Err) + ': ' + @ErrMsg
EXEC msdb.dbo.sp_send_dbmail
@recipients = @Email,
@body = @Body1,
@subject = 'An error occurred Archiving the AuditLog table.' ;
End
End

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1519016
Posted Monday, December 2, 2013 6:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 10:27 AM
Points: 999, Visits: 160
why don't you try the SQL Server Partitioning instead of trying to manually implement such a thing?
If I am understanding correctly, you only are doing this because the table is growing and you need a better performance?


Aram Koukia: http://www.koukia.ca
Post #1519050
Posted Monday, December 2, 2013 9:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 6:19 AM
Points: 147, Visits: 591
Table partitioning would be better solution for this, but be aware that Portioning is enterprise only feature.




Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com
Post #1519063
Posted Tuesday, December 3, 2013 5:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 233, Visits: 923
Where I need the better performance is in my archival process. I have to work around the inserts from Biztalk, to log to three different tables, at a rate of about 200 transactions per second. The tables have 75M to 200M records and I am trying to keep the DB from overflowing the disk space on this server. I created the script you see with the ability to adjust the batch sizes so I could tune it easily, on the fly, as I have to not overload the log as well.

The goal is to have each batch of records moved inside a transaction, so that no records are lost if there is any kind of issue when archiving that batch.
During testing I did not encounter more than on open transaction left over when I stopped the script before it was completed. It seemed to either commit or rollback all others and only be in the middle on one tran when I would stop the script at a random moment.

The other thing this is supposed to allow, for performance purposes, is the batch and transaction completes and commits or rolls back, and releases all locks so the other traffic, the inserts, can continue without hindrance, until the next delete from the archive process comes along. The reason I stopped this script yesterday was because of all of the locks it was holding, and stepping on the other processes doing inserts. After stopping the Agent job I noticed it rolling back for a long period of time, and that the rollback was putting records back into the Prod table at a rate of 1000 records at a time, the same size as the current batch setting. This is what prompted me to create this thread.

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1519185
Posted Tuesday, December 3, 2013 6:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 277, Visits: 329
As a minor suggestion, you could try wrapping entire script in a single named transaction and then continue using another named transaction for the "work". You could commit the "work" transaction or rollback as necessary. But the "work" transaction must be named. If not the whole batch gets rolled back.

I believe you are a victim of the "I didn't know it was wrapping the statement in an implied transaction" issue. I have been a victim several times in the past. The above suggestion has helped me out several times.

EDIT: From 2008R2 BOL: If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.
Post #1519192
Posted Tuesday, December 3, 2013 10:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 2,194, Visits: 3,301
The quick and very-dirty-in-this-case "solution" is to put a COMMIT TRANSACTION immediately before the END TRY.


But the whole process overall could be a lot cleaner and faster, particularly if:

auditlogid is the clustering key for the table, and continually ascending (such as an identity); and
logtimestamp is from the original insert, and so will also always ascend in conjunction with the auditlogid.

Are both of those true?



SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1519310
Posted Wednesday, December 4, 2013 7:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 233, Visits: 923
ScottPletcher, yes, AuditLogID is the clustered Index and an Identity column. I am always interested in better, faster ways to make things happen, if I can maintain data integrity.

I have figured it out, thanks to Google and an example from guys from 'rola. Turns out I was confused about how the Catch works, in that that code inside the Catch never gets executed unless there is a problem, so my Commit being inside the catch meant it would never execute unless there was an error, and even then, if there was an error and the Catch code was executed, my Error value would be greater than 0 and STILL not execute the Commit.
I changed it around so that the commit is in the Try and the rollback is in the catch, like this
:
While
Try
Select records from Audit into Temp table
Insert records from Temp table into Archive table
Delete records from Audit table
Drop temp table
Log to a table how many records we moved.
Commit Tran
End Try

Catch
@Error = ltrim(str(error_number()))
Rollback Tran
Break from loop.
End Catch

Outside of loop, Send email if there is a problem, @error <> 0.

I must have tested on such a small number of records that this problem slipped by me.

Thanks for the replies.

Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1519633
Posted Wednesday, December 4, 2013 7:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 2,194, Visits: 3,301
Stamey (12/4/2013)
I have figured it out, thanks to Google and an example from guys from 'rola. ... I changed it around so that the commit is in the Try and the rollback is in the catch
Chris


Sorry, I had very limited time for my post, but I still believe I pointed that out too, as the first line in my previous post .

The only reason I said it is "dirty" is that the entire code could be cleaned up to be much more efficient. But at least you have a working solution until that can be done.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1519644
Posted Wednesday, December 4, 2013 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 233, Visits: 923
Yes, your post did say that. In my case though, I found the other yesterday and didn't get back to this post until this morning. Either way, thanks.

If you would like to expand on your explanation regarding better performance, I'm all ears.
Here is the latest revision.
Set NOCOUNT ON
declare @enddate datetime, @NumRecordsToGet int, @FirstDayOfQuarter datetime
Declare @NumRecords Int, @NumAffected Int
Declare @Err Int, @ErrMsg VarChar(255), @Body1 VarChar(500), @Email VarChar(2000)

select @enddate = convert(datetime,convert(varchar,dateadd(month,-3,getdate()),101)), @FirstDayOfQuarter = DATEADD(qq, DATEDIFF(qq,0,GetDate()), 0)

If datediff(day,@FirstDayOfQuarter,GetDate()) > 5 --don't archive for the first 5 days of each quarter; in case something goes wrong with the backup
Begin
While (select logtimestamp from ACSLog.dbo.auditlog where auditlogid = (select min(auditlogid) from ACSLog.dbo.auditlog)) < @EndDate
Begin
Begin Try
If Object_ID('TempDB..#archive') > 0
Drop Table #archive
Select Top 1 @NumRecordsToGet = NumAuditRecords, @Email = ErrorEmail from ACSLog_Archive.dbo.PurgeLimits
Begin Tran
Select Top (@NumRecordsToGet) * into #Archive from ACSLog.dbo.auditlog With (NoLock) Where logtimestamp < @enddate --Populate Temp table.
Insert Into ACSLog_Archive.dbo.AuditLog --Put records into archive table where they do not exist in archive table.
Select A.* from #Archive A
Left Join ACSLog_Archive.dbo.AuditLog B On A.AuditLogID = B.AuditLogID
Where B.AuditLogID Is Null
Select @NumAffected = @@RowCount
Delete A
From ACSLog.dbo.AuditLog A With (Rowlock)
Inner Join ACSLog_Archive.dbo.AuditLog B On A.AuditLogID = B.AuditLogID
Drop table #Archive;
Insert Into ACSLog_Archive.dbo.PURGE_StatusLog (ArchivedCount, TableName)
Values (@NumAffected, 'AuditLog');
Set @NumAffected = 0 --Reset for next loop.
Commit Tran
End Try
BEGIN CATCH
IF @@TRANCOUNT > 0
RollBack Tran
Set @Err = ltrim(str(error_number()))
Set @ErrMsg = error_message()
PRINT 'ErrNo: ' + @Err
PRINT 'ErrMsg: ' + @ErrMsg
Break --out of while loop.
END CATCH
--Print 'TranCount: ' + Convert(VarChar(5), @@TranCount)
End
If @Err > 0
Begin
Set @Body1 = Convert(NVarChar(10), @Err) + ': ' + @ErrMsg
EXEC msdb.dbo.sp_send_dbmail
@recipients = @Email,
@body = @Body1,
@subject = 'An error occurred Archiving the AuditLog table.' ;
End
End

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1519685
Posted Wednesday, December 4, 2013 9:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 2,194, Visits: 3,301
Need to clarify whether the other part of my initial impressions was true or not:

Is logtimestamp from the original insert, and so will also always ascend in conjunction with the auditlogid? I'm hoping logtimestamp defaults to GETDATE() and so will always ascend with the auditlogid.

If it doesn't, you might need to build a separate index on ( logtimestamp, auditlogid ) for best performance, and the code might need written slightly differently for best performance.





SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1519702
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse