question about log file size when alter Huge table

I have the next question, and i would like to hear what do you think
about, and if is there a better solution for "my problem"

here is the question, I have a huge table with 60GB of data (image
files). The problem happen always when i try to ALTER the structure of
the table. For example I change a field char(3) to char(4)...the
sqlserver then performs the "alter table" command...that must be
something similar than "insert into the new table + drop the actual
table" and for that I need about 60GB o space for my LOG file, and
takes hours to complete the operation.

Is this the only way to alter a single field in my table??

I would like to heard you opinions...Thanks..

ALberto"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1130717670.681491.136070@.g47g2000cwa.googlegr oups.com...
> I have the next question, and i would like to hear what do you think
> about, and if is there a better solution for "my problem"
> here is the question, I have a huge table with 60GB of data (image
> files). The problem happen always when i try to ALTER the structure of
> the table. For example I change a field char(3) to char(4)...the
> sqlserver then performs the "alter table" command...that must be
> something similar than "insert into the new table + drop the actual
> table" and for that I need about 60GB o space for my LOG file, and
> takes hours to complete the operation.

Sounds about right.

> Is this the only way to alter a single field in my table??

Well, you might be able to ADD a new column to your table, that might work
better. (especially if it has no default).

Or, it might be easier to BCP the data out, truncate the table, BCP the data
in.

(in this case I don't think native format would work so you'd have to
experiment.)

> I would like to heard you opinions...Thanks..
> ALberto|||Gordowey (albertoiriarte@.gmail.com) writes:
> I have the next question, and i would like to hear what do you think
> about, and if is there a better solution for "my problem"
> here is the question, I have a huge table with 60GB of data (image
> files). The problem happen always when i try to ALTER the structure of
> the table. For example I change a field char(3) to char(4)...the
> sqlserver then performs the "alter table" command...that must be
> something similar than "insert into the new table + drop the actual
> table" and for that I need about 60GB o space for my LOG file, and
> takes hours to complete the operation.

Yup, that's it.

> Is this the only way to alter a single field in my table??

Rather than having ALTER TABLE to all that under the covers, you could do
it your self. The twist is that then you can do the insert in batches,
and truncate the transaction log between the turns. (Simplest is to run
in simple recovery mode if you can.) When you compose the batches, use
the clustered index for the table, else selection of the batches may be
horribly slow.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Question about dataasource and the .rdl file and the .rdl.data

Hi !

Maybe the only one who can answer this question is people from Microsoft but if someone have an idea just tell me ^_^ i really need a solution.

We have over 500 Access reports. I have imported all those reports but since there is some difference between Access and reportServer we have to make some changes on the reports.

Since i'll be too long to change all the reports one by one. I've created un program in .NET that open all the .rdl file. Those files are in XML so it's pretty easy to change all the things that doesn't work in ReportServer. So i've tried my program and everything is working fine except for the DataSource. When i look at the tag DataSource generated in the .rdl it seems that when we databind the report with the datasource it adds a Unique DataSource ID and also a new file .rdl.data
I've binded 2 reports with the same datasource and notice that the datasource ID is always different.

What i want to know is since all our report will use the same datasource (Shared). Can i use the same datasource ID for all the report and just copy one of those .rdl.date files for each report ?

I really need an answer because i'm afraid to have to changed all those reports one by one -_-

Thank a lot and Sorry about my english.

If the data sources are all the same, you can use a single shared data source.

The rdl.data file is used only by Report Designer. When you go live and publish the reports, you will not need the rdl.data files to view the reports. If you are using Access as the data source , you will need to have a path to the file all users can reach.

|||Thank for your answer!

I have already declare a shared datasource in our reportServer project. My problem is when i imported our access report in reportsercer the datasource set in the report is not the shared datasource. So i've tried to change in the XML code the tDatasource tag to bind our shared datasource but it doesn't work because i don't have an ID (i think).
I tried to bind 2 reports using visual studio to the shared datasource and notice that when you bind the report to the datasource a Datasource ID is added to the xml file. It seems that this ID is unique (Something like c5wcw-6fjhwj3-dwqw-ds2d2). So can i use the same id for all my reports?
|||I took a look at my current sub-project, 3 of 4 reports had exactly the same ID for the shared Datasource and one had another ID, so I copied the ID of the 3 others into that rdl and everything was still working.. I don't know how MS generates that IDs and what they are good for, but it seems you can use the same ID in all reports.. In my case neither the ID of the 3reports nor the ID for the last report matched the ID in the rds-File for the Datasource, so
just try to use the same <Datasources>...</Datasources> for all Reports..
|||

ok ! Thanks! I'll try this today and i'll post if it work or not.

Thanks !

|||

Everything is working fine !!!

Thank a lot !