On Mar 27, 12:16 am, Allan Mitchell <a...@no-spam.sqldts.com> wrote:
> Hello EMartinez,
>
> Why would committing results frequently cause the log file to grow? The
> reason the log file grows here is because nothing is committed yet and the
> results are stored in the log. Setting the log to simple will not stop it
> growing during a data pump but it will cause it to be flushed more frequently
> (But not during a transaction)
>
> Calling DBCC SHRINKDATABASE() wrecks your data on disk. data will get placed
> wherever it can and not optimally. You would need to REINDEX right after
> this.
>
> During the datapump we can retrieve and commit in batches allowing the log
> to breathe.
>
> --
>
> Allan Mitchellhttp://wiki.sqlis.com|http://www.sqlis.com|http://www.sqldts.com|http://www.konesans.com
>
> > It would seem like committing the results frequently would cause the
> > transaction log to grow faster. I would suggest either setting the
> > database recovery model to simple (if thats an option and you can get
> > access to it) or periodically calling DBCC SHRINKDATABASE (DBName, 20)
> > before or after transactions. DBCCs tend to be a little performance
> > intensive in general, but the benefits might out-weigh the risks in
> > your case. Hope this is helpful.
>
> > Regards,
>
> > Enrique Martinez
> > Sr. Software Consultant
> > Kayda wrote:
>
> >> I have a pump that does one data pump and then runs a series of
> >> sprocs (In Execute SQL tasks) on the data to transform. The
> >> transaction file is growing quite large (I don't have access
> >> unfortunately to the customer's production server). If the DTC
> >> service is not started, that means I cannot use transactions within
> >> the package, but I can still put transactions in my procedure. Is the
> >> best approach to put as many commits as possible in a long running
> >> sproc to keep the logfile small? Also, is there no way to commit the
> >> "INSERT" that the datapump does (target is SQL Server2000) before I
> >> start running my sprocs?
>
> >> Thanks,
> >> Kayda

> It would seem like committing the results frequently would cause the
> transaction log to grow faster. I would suggest either setting the
> database recovery model to simple (if thats an option and you can get
> access to it) or periodically calling DBCC SHRINKDATABASE (DBName, 20)
> before or after transactions. DBCCs tend to be a little performance
> intensive in general, but the benefits might out-weigh the risks in
> your case. Hope this is helpful.
>
> Regards,
>
> Enrique Martinez
> Sr. Software Consultant
> Kayda wrote:
>
>> I have a pump that does one data pump and then runs a series of
>> sprocs (In Execute SQL tasks) on the data to transform. The
>> transaction file is growing quite large (I don't have access
>> unfortunately to the customer's production server). If the DTC
>> service is not started, that means I cannot use transactions within
>> the package, but I can still put transactions in my procedure. Is the
>> best approach to put as many commits as possible in a long running
>> sproc to keep the logfile small? Also, is there no way to commit the
>> "INSERT" that the datapump does (target is SQL Server2000) before I
>> start running my sprocs?
>>
>> Thanks,
>> Kayda

"EMartinez" <emartinez.pr1@gmail.com> wrote in message
news:1174996906.630115.25870@l77g2000hsb.googlegroups.com...
> On Mar 27, 12:16 am, Allan Mitchell <a...@no-spam.sqldts.com> wrote:
>> Hello EMartinez,
>>
>> Why would committing results frequently cause the log file to grow? The
>> reason the log file grows here is because nothing is committed yet and
>> the
>> results are stored in the log. Setting the log to simple will not stop
>> it
>> growing during a data pump but it will cause it to be flushed more
>> frequently
>> (But not during a transaction)
>>
>> Calling DBCC SHRINKDATABASE() wrecks your data on disk. data will get
>> placed
>> wherever it can and not optimally. You would need to REINDEX right after
>> this.
>>
>> During the datapump we can retrieve and commit in batches allowing the
>> log
>> to breathe.
>>
>> --
>>
>> Allan
>> Mitchellhttp://wiki.sqlis.com|http://www.sqlis.com|http://www.sqldts.com|http://www.konesans.com
>>
>> > It would seem like committing the results frequently would cause the
>> > transaction log to grow faster. I would suggest either setting the
>> > database recovery model to simple (if thats an option and you can get
>> > access to it) or periodically calling DBCC SHRINKDATABASE (DBName, 20)
>> > before or after transactions. DBCCs tend to be a little performance
>> > intensive in general, but the benefits might out-weigh the risks in
>> > your case. Hope this is helpful.
>>
>> > Regards,
>>
>> > Enrique Martinez
>> > Sr. Software Consultant
>> > Kayda wrote:
>>
>> >> I have a pump that does one data pump and then runs a series of
>> >> sprocs (In Execute SQL tasks) on the data to transform. The
>> >> transaction file is growing quite large (I don't have access
>> >> unfortunately to the customer's production server). If the DTC
>> >> service is not started, that means I cannot use transactions within
>> >> the package, but I can still put transactions in my procedure. Is the
>> >> best approach to put as many commits as possible in a long running
>> >> sproc to keep the logfile small? Also, is there no way to commit the
>> >> "INSERT" that the datapump does (target is SQL Server2000) before I
>> >> start running my sprocs?
>>
>> >> Thanks,
>> >> Kayda
>
> Allan,
> You are entitled to your opinion; however, in terms of the DBCC
> SHRINKDATABASE (http://msdn2.microsoft.com/en-us/library/
> ms178037.aspx), you are completely incorrect. I have worked in
> environments where I maintained several databases that had millions of
> records and backups that were 400-500 GBs w/space used on 25 TB of SAN
> and DBCC SHRINKDATABASE has never harmed data on disk, database
> availability/performance or anything else. Sorry that you have been
> misinformed.
>
> Regards,
>
> Enrique Martinez
> Sr. Software Consultant
>