By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,992 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,992 IT Pros & Developers. It's quick & easy.

Updating a Chunk of data without crashing transaction log

P: n/a

Here is my dilema. I have a 120 GB database that I need to mask customer
credit card numbers in. The field is a varchar (16). I need to update
the field so that we only store the first 4 numbers and the last 4
numbers of the credit card and insert * to fill in the rest of the
credit card number.

I was going to do this as a loop using the following code:

While Exists (Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)
Begin
Begin Transaction T1
Update
Header
Set
Header.CCNbr = Left (D1.CCNbr, 4) + '********' + Right (D1.CCNbr, 4)
From
(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1) as D1
Commit Transaction T1

If Not Exists(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)
Break
Else
Continue
End

In theory this only selects the top 10 rows, updates them, dumps the log
and moves on to the next 10 until all the rows are updated.

I tried running this on my test database and it fills up the transaction
log.

Can anyone tell me the best way to go about doing what I need?

Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Nov 2 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Chris Johnson (cj******@cskauto.com) writes:
Here is my dilema. I have a 120 GB database that I need to mask customer
credit card numbers in. The field is a varchar (16). I need to update
the field so that we only store the first 4 numbers and the last 4
numbers of the credit card and insert * to fill in the rest of the
credit card number.

I was going to do this as a loop using the following code:

While Exists (Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)
Begin
Begin Transaction T1
Update
Header
Set
Header.CCNbr = Left (D1.CCNbr, 4) + '********' + Right
(D1.CCNbr, 4)
From
(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1) as D1
Commit Transaction T1

If Not Exists(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)
Break
Else
Continue
End

In theory this only selects the top 10 rows, updates them, dumps the log
and moves on to the next 10 until all the rows are updated.

I tried running this on my test database and it fills up the transaction
log.


So what recovery mode do you have? If you have full or bulk-logged
recovery, batching does not help, if you don't every now and then
back up the transaction log. If you have simple recovery mode, SQL Server
truncates the log often.

Normally, a production database has full recovery. However, for a wild
operation like this one, it's reasonable to switch to simple mode, do
the operation, change back, and then take a full backup.

Now to your Update statement. Since you have two tables and no restriction
on Header, your updating each row in Header each time. And you are updating
it ten times...

Here is a better way to do it. I've increased the batch size, since ten is
far too low.

SET ROWCOUNT 100000
WHILE 1 = 1
BEGIN
UPDATE Header
SET CCNbr = Left (CCNbr, 4) + '********' + Right (CCNbr, 4)
WHERE CCNbr NOT LIKE '%*%'
SELECT @err = @@error, @rowc = @@rowcount
IF @err <> OR @rowc < 100000
BREAK
END
SET ROWCOUNT 0

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 2 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.