I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...
Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...
|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>
Showing posts with label nvar. Show all posts
Showing posts with label nvar. Show all posts
Tuesday, March 20, 2012
Change datatype var to nvar but log gets full
I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>
Change datatype var to nvar but log gets full
I have a table with a column of datatype var and it has to be changed to nva
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql
Subscribe to:
Posts (Atom)