Run an SP without adding anything to the transaction log?

Hi, I have an SP that will basically copy one table's data to another with
some transformation involved. The source table over 1 billion rows. The
machine has limited disk space and the transaction log will grow too large
and cause trouble.
Is there a way to run this SP and not have the transactions logged for it?
Thanks,
ADAMWhat kind of transformation?
If you can use a select statement to bcp the data to a file, then you can
change the recovery model (first make a full db backup) to bulk-logged, and
use bcp utility or bulk insert statement to insert the data into the second
table. Change back the recovery model to full when you finish.
AMB
"adami" wrote:

> Hi, I have an SP that will basically copy one table's data to another with
> some transformation involved. The source table over 1 billion rows. The
> machine has limited disk space and the transaction log will grow too large
> and cause trouble.
> Is there a way to run this SP and not have the transactions logged for it?
> Thanks,
> ADAM|||Hi,
Set the database into Simple recovery Recovery model. After that execute the
Insert statment in batches of 1000 or 2000. Commit the transaction after
each batch. This will clear the Transaction log and will ensure that LDF
will not grow.
Thanks
Hari
SQL Server MVP
"adami" <adami@.discussions.microsoft.com> wrote in message
news:44698021-D132-412F-8BC2-0C47D08AA976@.microsoft.com...
> Hi, I have an SP that will basically copy one table's data to another with
> some transformation involved. The source table over 1 billion rows. The
> machine has limited disk space and the transaction log will grow too large
> and cause trouble.
> Is there a way to run this SP and not have the transactions logged for it?
> Thanks,
> ADAM|||To start, read up the setting the database's recovery model to simple, even
if temporarily for this process. Also, you could see if setting the database
to SINGLE_USER mode during the process is possible. Dropping indexes from
the destination table prior to the inserts and then re-creating them again
afterward will also reduce the number of transaction, speed things up and
reduce index fragmentation.
I have a data warehousing project where I have a similar situation, except
my row volume is only around 10,000,000 instead of 1,000,000,000, but still
a dump load of data. What I actually did was to create a view of the source
table which performs the needed transformations, and a DTS package exports
the view to a tab delimited text file. Once the export is complete, I bulk
copy (BCP.EXE) the text file into the destination table. Read up on the bulk
copy feature and how it can be used to move data with minimal logging. Of
everything I tried, this was by far the fastest.
"adami" <adami@.discussions.microsoft.com> wrote in message
news:44698021-D132-412F-8BC2-0C47D08AA976@.microsoft.com...
> Hi, I have an SP that will basically copy one table's data to another with
> some transformation involved. The source table over 1 billion rows. The
> machine has limited disk space and the transaction log will grow too large
> and cause trouble.
> Is there a way to run this SP and not have the transactions logged for it?
> Thanks,
> ADAM

Run a global variable dynamically

I have a SSIS pkg with several task. I need to run a global variable dynamically.

the global variable's value is basically a bat file with two arguments (\\.....\..bat \\......\.xls \\.....\...xls)

bat file arg1 arg2

This global variable gets set from its previous task at run time. I did put in this global variable as an argument in expression and for executable i am calling the cmd.exe file. But for some reason it does not work. the command prompt comes up wait for an action from user which is wrong. Please help

Thanks

Make sure that you have a full path to the batch file.

Also, make sure you are using the "/C" flag before the path to the batch file.

Command should look like:

cmd.exe /C \\server\mybatchfile.bat arg1 arg2......|||

Thanks Phil. That worked.

RUN "Intergration Services Deployment Manifest" through Command Prompt

Hi, I want to run "Intergration Services Deployment Manifest" through the command prompt, passing the values .. which should basically go and install that Package either on File System or SQL Server. Does any one have any idea on how to do that ...

Thanks,

You can use the dtutil command prompt utility to copy a package to the Microsoft SQL Server database, the SSIS Package Store, or the file system.

However, the package must already exist in one of these three locations.

For more information, see "dtutil Utility" at http://msdn2.microsoft.com/en-us/library/ms162820.aspx.

|||Thanks alot for your reply ..

RUN "Intergration Services Deployment Manifest" through Command Prompt

Hi, I want to run "Intergration Services Deployment Manifest" through the command prompt, passing the values .. which should basically go and install that Package either on File System or SQL Server. Does any one have any idea on how to do that ...

Thanks,

You can use the dtutil command prompt utility to copy a package to the Microsoft SQL Server database, the SSIS Package Store, or the file system.

However, the package must already exist in one of these three locations.

For more information, see "dtutil Utility" at http://msdn2.microsoft.com/en-us/library/ms162820.aspx.

|||Thanks alot for your reply ..