Bulk UPDATE on SQL Server 2000

I need to do a bulk update of 1 field in a data-table (removing
zero-padding). The update is expected to update 52.5 million records.
What techniques could I use so I can ensure
1) Consistency in the transactional log backups (every 10 mins)
2) Consistency in the full DB backup every day
3) Disk drive of Transaction logs do not get filled up completely (I suspect
even if I set rown count to 10,000 and loop through, disk-space would still
be used')
Target environment
SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
(Different disk drives for data and log files)A normal set based operation will do fine. update my table set myfield ='mynewvalue'
However, this will create significant locking so you're only going to be
this aggressive on a database that's not in use (Overnight, for most) or
write a cursor that includes some logic in it but that's going to be slow.
--
DatabaseAdmins.com
Databases Reliable. Available.
"Patrick" wrote:
> I need to do a bulk update of 1 field in a data-table (removing
> zero-padding). The update is expected to update 52.5 million records.
> What techniques could I use so I can ensure
> 1) Consistency in the transactional log backups (every 10 mins)
> 2) Consistency in the full DB backup every day
> 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> even if I set rown count to 10,000 and loop through, disk-space would still
> be used')
> Target environment
> SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> (Different disk drives for data and log files)|||SQL Server takes care of consistency. To make sure you don't blow up the
transaction log, dividing the update into smaller chunks of updates (e.g.
10,000 rows at a time as you mentioned) is the right approach in general.
I'd like to add that you may have to pace your updates (e.g. sleep for some
time after each iteration) so that your transaction log backups at the
10-minute interval have a chance coming in to clear out the inactive portion
of the log for reuse. Alternative, you can backup the transaction log after
each iteration (or every so many iterations) in the loop. You need to
experiment a bit to find out the best chunk size and whether the loop without
any wait time would be too tight in your particular configuration.
Also, it's certainly possible you can choose to update all the rows in a
single shot, depending on the size of your transaction log.
Linchi
"burt_king" wrote:
> A normal set based operation will do fine. update my table set myfield => 'mynewvalue'
> However, this will create significant locking so you're only going to be
> this aggressive on a database that's not in use (Overnight, for most) or
> write a cursor that includes some logic in it but that's going to be slow.
> --
> DatabaseAdmins.com
> Databases Reliable. Available.
>
> "Patrick" wrote:
> > I need to do a bulk update of 1 field in a data-table (removing
> > zero-padding). The update is expected to update 52.5 million records.
> >
> > What techniques could I use so I can ensure
> > 1) Consistency in the transactional log backups (every 10 mins)
> > 2) Consistency in the full DB backup every day
> > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > even if I set rown count to 10,000 and loop through, disk-space would still
> > be used')
> >
> > Target environment
> > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > (Different disk drives for data and log files)|||I have composed the following SQL, could you tell me what is wrong? The
Select getDate() only printed <10 dates! when I am expecting millions of
records to be updated!
DECLARE @.recCount int
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_transactions
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_stocks
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_inventory
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
"Linchi Shea" wrote:
> SQL Server takes care of consistency. To make sure you don't blow up the
> transaction log, dividing the update into smaller chunks of updates (e.g.
> 10,000 rows at a time as you mentioned) is the right approach in general.
> I'd like to add that you may have to pace your updates (e.g. sleep for some
> time after each iteration) so that your transaction log backups at the
> 10-minute interval have a chance coming in to clear out the inactive portion
> of the log for reuse. Alternative, you can backup the transaction log after
> each iteration (or every so many iterations) in the loop. You need to
> experiment a bit to find out the best chunk size and whether the loop without
> any wait time would be too tight in your particular configuration.
> Also, it's certainly possible you can choose to update all the rows in a
> single shot, depending on the size of your transaction log.
> Linchi
> "burt_king" wrote:
> > A normal set based operation will do fine. update my table set myfield => > 'mynewvalue'
> >
> > However, this will create significant locking so you're only going to be
> > this aggressive on a database that's not in use (Overnight, for most) or
> > write a cursor that includes some logic in it but that's going to be slow.
> >
> > --
> > DatabaseAdmins.com
> > Databases Reliable. Available.
> >
> >
> > "Patrick" wrote:
> >
> > > I need to do a bulk update of 1 field in a data-table (removing
> > > zero-padding). The update is expected to update 52.5 million records.
> > >
> > > What techniques could I use so I can ensure
> > > 1) Consistency in the transactional log backups (every 10 mins)
> > > 2) Consistency in the full DB backup every day
> > > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > > even if I set rown count to 10,000 and loop through, disk-space would still
> > > be used')
> > >
> > > Target environment
> > > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > > (Different disk drives for data and log files)

BUILTIN/Administrators

Hello All,
SQL Server 2k SP3
Can anyone advise me on pros and cons of removing BUILTIN/Administrators ?
Thanks,
BivaDon't forget to check if there are any apps mapped with
administrator accounts under your environment. Removing
BUILTIN\Administrators you will get extra security
>--Original Message--
>Hello All,
>SQL Server 2k SP3
>Can anyone advise me on pros and cons of removing
BUILTIN/Administrators ?
>Thanks,
>Biva
>
>.
>|||Microsoft published a white paper or knowledge base
article on how to remove this group but the number
escapes me at the moment. I can tell you that you will
need to modify your sqlagent and any apps using this
group, also it is better to completely remove this group
rather than disable or demote its permissions.
I have been at several differant companies and we have
always removed this group to tighten security and it is
not very hard to do but produces a major security benefit.
>--Original Message--
>Don't forget to check if there are any apps mapped with
>administrator accounts under your environment. Removing
>BUILTIN\Administrators you will get extra security
>
>>--Original Message--
>>Hello All,
>>SQL Server 2k SP3
>>Can anyone advise me on pros and cons of removing
>BUILTIN/Administrators ?
>>Thanks,
>>Biva
>>
>>.
>.
>|||The SQL Server 2000 BUILTIN\Administrators login account
maps to the local Administrators group on the local
computer where SQL Server 2000 is installed. The local
Administrators group includes the Domain Administrators
group. The BUILTIN\Administrators group is by default a
member of the sysadmin fixed server role, to which the sa
login account maps in SQL Server 2000. Thus, anyone who
is a Domain Administrator or a local Administrator will
automatically be a member of the sysadmin fixed server
role for that SQL Server installation and possibly other
installations. The DBA can resolve this by removing the
BUILTIN\Administrators account from the sysadmin fixed
server role.
This practice is recommended by Microsoft.
-George Jiang
Microsoft MCDBA
>--Original Message--
>Don't forget to check if there are any apps mapped with
>administrator accounts under your environment. Removing
>BUILTIN\Administrators you will get extra security
>
>>--Original Message--
>>Hello All,
>>SQL Server 2k SP3
>>Can anyone advise me on pros and cons of removing
>BUILTIN/Administrators ?
>>Thanks,
>>Biva
>>
>>.
>.
>|||Hi Biva,
Thank you for using MSDN Newsgroup! My name is Billy and it's my pleasure
to assist you with this issue.
From your description, I understand you want to know what's the advantages
and disadvantages of removing the BUILTIN/Administrators Login in SQL
Server. Have I fully understood you? If there is anything I misunderstood,
please feel free to let me know.
==========Removing this Login IMPEDEs Windows NT system administrators from having
system administrator (sa) privileges in SQL Server, and this is for the
security benefits.
By default, the SQL Server setup creates the "BUILTIN\Administrators"
login, which gives any account in the Local Administrators group system
administrator (sa) privileges.
However, in some environments, you might not want to allow Microsoft
Windows NT system administrators to have this kind of access to SQL Server.
On a stand-alone server that is running SQL Server, you can remove the
"BUILTIN\Administrators" login from SQL Server to limit this type of access.
==========Before you removing this login, as other community member reminded you,
it's better to ensure that there are no services and application
using/mapping with the administrator accounts. Otherwise, you may meet with
some possible problems due to the removal of this login:
237604 PRB: SQL Server Agent Does Not Start and Displays Error 18456
http://support.microsoft.com/?id=237604
291255 BUG: IsAlive Check Does Not Run Under the Context of the
http://support.microsoft.com/?id=291255
295034 FIX: Microsoft Search Service May Cause 100% CPU Usage if
http://support.microsoft.com/?id=295034
274446 INF: Upgrade to SQL Server 2000 Failover Solution Recommended for All
http://support.microsoft.com/?id=274446
================On a clustered SQL Server, the removal of the "BUILTIN\Administrators"
login might cause problems. If the server is clustered and you remove the
"BUILTIN\Administrators" login, the SQL Server resource goes into an online
pending state and eventually fails.
However, if you remove the "BUILTIN\Administrators" login before you run
the Failover Cluster Wizard, the process completes successfully because the
Failover Cluster Wizard explicitly adds a SQL Server login for the accounts
that need access.
=================In SQL Server 6.5 and SQL Server 7.0
To limit Windows NT Administrators access as system administrator (sa) on a
SQL Server cluster, follow these steps:
1. Explicitly add the account that is being used for the Cluster Service as
a SQL Server login. You must give this login the System Administrator role.
Note if the server that is running SQL Server is unclustered and
reclustered, you must repeat this process.
2. Remove the "BUILTIN\Administrators" login from SQL Server after the SQL
Server Failover Cluster Wizard successfully clusters the SQL Server
installation.
---
-- To remove BUILTIN\Administrators.
sp_revokelogin [BUILTIN\Administrators]
---
===============In SQL Server 2000
To limit Windows NT Administrators access as system administrator (sa) on
a SQL Server cluster, follow these steps:
1. Explicitly add the account that is being used for the Cluster Service as
a SQL Server login. You must give this login the System Administrator role.
If full-text search is to be used on the cluster, you must add the [NT
Authority\System] account to the server sysadmin group.
For example:
---
-- Grant [NT Authority\System] a logon to SQL Server:
EXEC sp_grantlogin [NT Authority\System]
-- Add that account to the sysadmins role:
EXEC sp_addsrvrolemember @.loginame = [NT Authority\System], @.rolename ='sysadmin'
---
2. Remove the "BUILTIN\Administrators" login from SQL Server after you
install the virtual server.
---
-- To remove BUILTIN\Administrators.
EXEC sp_revokelogin [BUILTIN\Administrators]
---
Biva, does this answer your question?
If there is anything more I can do to assist you, please feel free to post
it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.