Not writing to the transaction log - May need a guru here.....

[gurmi]

Good afternoon one and all,

I have the following problem :

I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.

Does any1 know how I can make these changes without SQL writing to the transaction log?

Any and all help appreciated,

Thanks

Gurmi

Dump the log periodically or restrict the log.

------------
gurmi at 1/3/01 11:04:31 AM


Good afternoon one and all,

I have the following problem :

I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.

Does any1 know how I can make these changes without SQL writing to the transaction log?

Any and all help appreciated,

Thanks

Gurmi

[Gurinder Singh Kohli]

There are restrictions, Logging will still be done.

Try running your process in batches, say committing after every 1000 records
and Dumping Log. This will be possible if you have some kind of primary
key, etc.

Hope this helps.

Gurinder Singh Kohli

------------
xiao at 1/3/01 2:58:57 PM

simple,
bcp out,
enable database option 'select into',
truncate table, modify it
bcp in


------------
gurmi at 1/3/01 11:04:31 AM


Good afternoon one and all,

I have the following problem :

I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.

Does any1 know how I can make these changes without SQL writing to the transaction log?

Any and all help appreciated,

Thanks

Gurmi

[Scott Whigham]

Mark's suggestion of enabling TRUNCATE LOG ON CHECKPOINT is the simplest. Depending on your recovery interval settings (set it to 0 and Checkpoint will happen about once a minute)

This will prevent the Tran log from filling up for sure BUT! It will also invalidate your transaction log if you had to use it for recovery so you will need to turn this option off and then issue a full database backup (or differential).

Good luck

Scott

http://www.gulfsouthconsulting.com/sqlserver


------------
Gurinder Singh Kohli at 1/3/01 3:36:53 PM


There are restrictions, Logging will still be done.

Try running your process in batches, say committing after every 1000 records
and Dumping Log. This will be possible if you have some kind of primary
key, etc.

Hope this helps.

Gurinder Singh Kohli

------------
xiao at 1/3/01 2:58:57 PM

simple,
bcp out,
enable database option 'select into',
truncate table, modify it
bcp in


------------
gurmi at 1/3/01 11:04:31 AM


Good afternoon one and all,

I have the following problem :

I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.

Does any1 know how I can make these changes without SQL writing to the transaction log?

Any and all help appreciated,

Thanks

Gurmi

[Gurinder Singh Kohli]

Scott

Try it and let me know, if worked, because such kind of
updates happen in one Batch so Log will still be used.

Doing in batches will be the way to go.

Thaxns.

Gurinder Singh Kohli

------------
Scott Whigham at 1/3/01 3:43:42 PM

Mark's suggestion of enabling TRUNCATE LOG ON CHECKPOINT is the simplest. Depending on your recovery interval settings (set it to 0 and Checkpoint will happen about once a minute)

This will prevent the Tran log from filling up for sure BUT! It will also invalidate your transaction log if you had to use it for recovery so you will need to turn this option off and then issue a full database backup (or differential).

Good luck

Scott

http://www.gulfsouthconsulting.com/sqlserver


------------
Gurinder Singh Kohli at 1/3/01 3:36:53 PM


There are restrictions, Logging will still be done.

Try running your process in batches, say committing after every 1000 records
and Dumping Log. This will be possible if you have some kind of primary
key, etc.

Hope this helps.

Gurinder Singh Kohli

------------
xiao at 1/3/01 2:58:57 PM

simple,
bcp out,
enable database option 'select into',
truncate table, modify it
bcp in


------------
gurmi at 1/3/01 11:04:31 AM


Good afternoon one and all,

I have the following problem :

I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.

Does any1 know how I can make these changes without SQL writing to the transaction log?

Any and all help appreciated,

Thanks

Gurmi

[Scott Whigham]

Will it all happen as one batch? I don't know - and I don't have a test server with enough rows to make a test of it. I guess you could do it with a WHERE statement and tell it to do A-B first, then C-D, etc assuming there is a PK on some field that starts with a alpha character. This would force batches to occur and the checkpointing would certainly occur and flush the written pages to disk and out of the log.

Hopefully all of this discourse helps you find a good way to do this.


------------
Gurinder Singh Kohli at 1/3/01 5:23:52 PM



Scott

Try it and let me know, if worked, because such kind of
updates happen in one Batch so Log will still be used.

Doing in batches will be the way to go.

Thaxns.

Gurinder Singh Kohli

------------
Scott Whigham at 1/3/01 3:43:42 PM

Mark's suggestion of enabling TRUNCATE LOG ON CHECKPOINT is the simplest. Depending on your recovery interval settings (set it to 0 and Checkpoint will happen about once a minute)

This will prevent the Tran log from filling up for sure BUT! It will also invalidate your transaction log if you had to use it for recovery so you will need to turn this option off and then issue a full database backup (or differential).

Good luck

Scott

http://www.gulfsouthconsulting.com/sqlserver


------------
Gurinder Singh Kohli at 1/3/01 3:36:53 PM


There are restrictions, Logging will still be done.

Try running your process in batches, say committing after every 1000 records
and Dumping Log. This will be possible if you have some kind of primary
key, etc.

Hope this helps.

Gurinder Singh Kohli

------------
xiao at 1/3/01 2:58:57 PM

simple,
bcp out,
enable database option 'select into',
truncate table, modify it
bcp in


------------
gurmi at 1/3/01 11:04:31 AM


Good afternoon one and all,

I have the following problem :

I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.

Does any1 know how I can make these changes without SQL writing to the transaction log?

Any and all help appreciated,

Thanks

Gurmi

[gurmi]

------------
Scott Whigham at 1/3/01 5:55:04 PM

Will it all happen as one batch? I don't know - and I don't have a test server with enough rows to make a test of it. I guess you could do it with a WHERE statement and tell it to do A-B first, then C-D, etc assuming there is a PK on some field that starts with a alpha character. This would force batches to occur and the checkpointing would certainly occur and flush the written pages to disk and out of the log.

Hopefully all of this discourse helps you find a good way to do this.


------------
Gurinder Singh Kohli at 1/3/01 5:23:52 PM



Scott

Try it and let me know, if worked, because such kind of
updates happen in one Batch so Log will still be used.

Doing in batches will be the way to go.

Thaxns.

Gurinder Singh Kohli

------------
Scott Whigham at 1/3/01 3:43:42 PM

Mark's suggestion of enabling TRUNCATE LOG ON CHECKPOINT is the simplest. Depending on your recovery interval settings (set it to 0 and Checkpoint will happen about once a minute)

This will prevent the Tran log from filling up for sure BUT! It will also invalidate your transaction log if you had to use it for recovery so you will need to turn this option off and then issue a full database backup (or differential).

Good luck

Scott

http://www.gulfsouthconsulting.com/sqlserver


------------
Gurinder Singh Kohli at 1/3/01 3:36:53 PM


There are restrictions, Logging will still be done.

Try running your process in batches, say committing after every 1000 records
and Dumping Log. This will be possible if you have some kind of primary
key, etc.

Hope this helps.

Gurinder Singh Kohli

------------
xiao at 1/3/01 2:58:57 PM

simple,
bcp out,
enable database option 'select into',
truncate table, modify it
bcp in


------------
gurmi at 1/3/01 11:04:31 AM


Good afternoon one and all,

I have the following problem :

I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.

Does any1 know how I can make these changes without SQL writing to the transaction log?

Any and all help appreciated,

Thanks

Gurmi