Removing a server: million

Showing posts with label million. Show all posts

Wednesday, March 21, 2012

Rename Column in Very large table

I would like to rename a column in a table that has over 770 million rows and
around 300-400 gigs of data. I would like to minimize time, resources, and
risk. I am looking for suggestions, risks, or any other gotcha's i may not be
considering.Check out sp_rename in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>I would like to rename a column in a table that has over 770 million rows
>and
> around 300-400 gigs of data. I would like to minimize time, resources, and
> risk. I am looking for suggestions, risks, or any other gotcha's i may not
> be
> considering.|||Does this have any impact in the size of the transaction log, or since i
believe it uses tempdb will it cause any errors due to log file size or disc
space issues?
in Enterprise manager in design mode the gui will handle the name change but
i'm more worried about the impact it may have to log file size ...ect
"Tom Moreau" wrote:
> Check out sp_rename in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
> >I would like to rename a column in a table that has over 770 million rows
> >and
> > around 300-400 gigs of data. I would like to minimize time, resources, and
> > risk. I am looking for suggestions, risks, or any other gotcha's i may not
> > be
> > considering.
>
>|||An object rename goes very quickly and has practically no impact on the log.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
> Does this have any impact in the size of the transaction log, or since i
> believe it uses tempdb will it cause any errors due to log file size or
> disc
> space issues?
> in Enterprise manager in design mode the gui will handle the name change
> but
> i'm more worried about the impact it may have to log file size ...ect
> "Tom Moreau" wrote:
>> Check out sp_rename in the BOL.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>> >I would like to rename a column in a table that has over 770 million
>> >rows
>> >and
>> > around 300-400 gigs of data. I would like to minimize time, resources,
>> > and
>> > risk. I am looking for suggestions, risks, or any other gotcha's i may
>> > not
>> > be
>> > considering.
>>|||How about the TempDB size, what effect does this have on the size of this db.
Do you need atleast the same size of free space on the tempdb to successfully
complete the rename?
"Tom Moreau" wrote:
> An object rename goes very quickly and has practically no impact on the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
> > Does this have any impact in the size of the transaction log, or since i
> > believe it uses tempdb will it cause any errors due to log file size or
> > disc
> > space issues?
> >
> > in Enterprise manager in design mode the gui will handle the name change
> > but
> > i'm more worried about the impact it may have to log file size ...ect
> >
> > "Tom Moreau" wrote:
> >
> >> Check out sp_rename in the BOL.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >>
> >> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> >> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
> >> >I would like to rename a column in a table that has over 770 million
> >> >rows
> >> >and
> >> > around 300-400 gigs of data. I would like to minimize time, resources,
> >> > and
> >> > risk. I am looking for suggestions, risks, or any other gotcha's i may
> >> > not
> >> > be
> >> > considering.
> >>
> >>
> >>
>
>|||There is no impact on tempdb during an object rename.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:ED637B0F-7A3E-40B5-81E2-B6445C9D5381@.microsoft.com...
> How about the TempDB size, what effect does this have on the size of this
> db.
> Do you need atleast the same size of free space on the tempdb to
> successfully
> complete the rename?
> "Tom Moreau" wrote:
>> An object rename goes very quickly and has practically no impact on the
>> log.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
>> > Does this have any impact in the size of the transaction log, or since
>> > i
>> > believe it uses tempdb will it cause any errors due to log file size or
>> > disc
>> > space issues?
>> >
>> > in Enterprise manager in design mode the gui will handle the name
>> > change
>> > but
>> > i'm more worried about the impact it may have to log file size ...ect
>> >
>> > "Tom Moreau" wrote:
>> >
>> >> Check out sp_rename in the BOL.
>> >>
>> >> --
>> >> Tom
>> >>
>> >> ----
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> SQL Server MVP
>> >> Columnist, SQL Server Professional
>> >> Toronto, ON Canada
>> >> www.pinpub.com
>> >>
>> >> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> >> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>> >> >I would like to rename a column in a table that has over 770 million
>> >> >rows
>> >> >and
>> >> > around 300-400 gigs of data. I would like to minimize time,
>> >> > resources,
>> >> > and
>> >> > risk. I am looking for suggestions, risks, or any other gotcha's i
>> >> > may
>> >> > not
>> >> > be
>> >> > considering.
>> >>
>> >>
>> >>
>>

Monday, March 12, 2012

Removing rows from very large table with indices

Hello,
I've got a table with a few million rows in it. This table has one clustere
d index and four other indices. When I try to delete records from it on a d
ay-by-day basis (it has a date column), it typically takes between 2 and 5 m
inutes to remove just one d
ay worth. I need to purge about 90 days worth of data ...
Is there a way to shut off index recomputation or whatever it is that takes
so long for the delete to occur? This is an operational data table that rea
lly only needs about 7 days worth of data in it (not 90).
Thanks
-- JakeHi Jake.
How are you performing the delete statement - row by row or by a single
statement supported by a where clause?
It's important to understand that the indexes will actually help the delete
to identify the rows that meet the criteria for deletion, so "shutting" down
the index during deletion would cause the unenviable effect that a table
scan would be required to identify the rows for deletion...
Regards,
Greg Linwood
SQL Server MVP
"javatopia" <anonymous@.discussions.microsoft.com> wrote in message
news:8D297AF5-9D9A-438F-9A76-971F9DF2D0C1@.microsoft.com...
> Hello,
> I've got a table with a few million rows in it. This table has one
clustered index and four other indices. When I try to delete records from
it on a day-by-day basis (it has a date column), it typically takes between
2 and 5 minutes to remove just one day worth. I need to purge about 90 days
worth of data ...
> Is there a way to shut off index recomputation or whatever it is that
takes so long for the delete to occur? This is an operational data table
that really only needs about 7 days worth of data in it (not 90).
> Thanks
> -- Jake
>|||Hi,
I don't recommend you to delete indexes because proper indexes will help you
to locate the records that you want to delete more quickly . (You have to
find the records first before you delete them.)
If you want to delete records in a single transaction (and if you are deleti
ng large amount of data) your log file will grow fast no matter what you're
recovery model is.
I'll prefer to set rowcount and delete records in a while loop until @.@.rowco
unt = 0. This will delete you're records in smaller transactions.
Also selecting the records that you like to keep in a new table and dropping
the old table and renaming the new table is generally less expensive then d
elete. (Depends the percentage of tha data that you want to keep and you wan
t to delete.)
And partitioned tables are very helpfull for archiving and deleting..
Regards..
Umut Nazl?ca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)|||I like the suggestion of selecting INTO a new table, dropping the old table,
and then renaming the new table to the old one. Seems like that could be d
one in an automated manner as well, right? Is it possible to put the DB int
o single-user mode via a st
ored procedure, then do all of this (select into, drop old, rename table)?
Your help is very much appreciated!!
-- Jake|||Hi,
Yes you can put db into single user mode using alter database and terminatio
n options like:
ALTER DATABASE XXX
SET SINGLE_USER WITH ROLLBACK AFTER X (See Books Online ..)
and you can schedule a job to automate it.
My suggestion is first script your table then create your new table using th
is script (at this point do not create the indexes on new table), transfer
data, create indexes on new table and so on.
But test first to see if it works the way you want..
Regards..

Saturday, February 25, 2012

Removing duplicate dat

Hi
I have a 1.5 million row table with a text field. Can
anybody think of a way to remove any duplicate rows where the text field
contains the same data (and a
way which will not take days to run!)
For example if I have two rows with "SQL SERVER IS GREAT", I want to remove
one of them.
Thanks
here is an example of something that works for me:
create table textstuff
(pk int not null primary key,
textcol text)
go
--insert statements
declare @.int int
select @.int=max(datalength(textcol)) from textstuff
select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
textstuff order by 2
select pk, holding.checksum from holding,
(select checksum, test=count(checksum) from holding group by checksum having
count(checksum) >1) as a
where holding.checksum=a.checksum
rows which have identical checksums will show up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul" <xx@.nospam.com> wrote in message
news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a 1.5 million row table with a text field. Can
> anybody think of a way to remove any duplicate rows where the text field
> contains the same data (and a
> way which will not take days to run!)
> For example if I have two rows with "SQL SERVER IS GREAT", I want to
remove
> one of them.
> Thanks
>
>
|||That's awsome, thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u4beTGwuEHA.3808@.TK2MSFTNGP15.phx.gbl...
> here is an example of something that works for me:
> create table textstuff
> (pk int not null primary key,
> textcol text)
> go
> --insert statements
> declare @.int int
> select @.int=max(datalength(textcol)) from textstuff
> select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
> textstuff order by 2
> select pk, holding.checksum from holding,
> (select checksum, test=count(checksum) from holding group by checksum
having
> count(checksum) >1) as a
> where holding.checksum=a.checksum
>
> rows which have identical checksums will show up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Paul" <xx@.nospam.com> wrote in message
> news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> remove
>

Wednesday, March 21, 2012

Rename Column in Very large table

Rename Column in Very large table

Rename Column in Very large table

Monday, March 12, 2012

Removing rows from very large table with indices

Saturday, February 25, 2012

Removing duplicate dat

Removing a server

Blog Archive

About Me