PHWinfo - Afficher un message - begin and end transaction and transaction log

13/12/2007, 01h34

"Kruton" <wmlyerly@gmail.com> wrote in message
news:a8d08495-59a1-4090-8906-2a9ff8b01945@o42g2000hsc.googlegroups.com...
> Hi Erlang,
> This is part of a large OLAP process that runs many times a day. I do
> not want to / need to restore to a particular time. I have a dba that
> does full backups on a regular basis. I would agree with you to a
> certain extent if this were OLTP but it is not.

Then your DBA needs to set the DBA to simple recovery.

>
> Thanks.
>
> On Dec 12, 2:18 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>> Kruton (wmlye...@gmail.com) writes:
>> > This is more of an architectural question about SQL Server. Can
>> > someone please explain why when I perform a query such as the one
>> > below that updates a table using begin and end transaction I am unable
>> > to programmatically truncate the transaction log. The only way I have
>> > found to truncate the transaction log is to stop and start the SQL
>> > Server Service. Does this transaction use the tempdb? Is that why I
>> > am unable to truncate the transaction log? Is there a better way to
>> > do this?
>>
>> > Begin trans T1
>>
>> > Update sometable
>> > Set random_row = 'blah'
>>
>> > End trans T1
>>
>> Why would you truncate the transaction log in the first place?
>>
>> If you run with full recovery and want to be table to restore to a point
>> in time, the you should backup your transaction log regularly.
>>
>> If you don't care about the point-in-time restores but are content with
>> restoring from a full backup in case of a failure, you should set the
>> database in simple recovery.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>>
>> Books Online for SQL Server 2005
>> athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>> Books Online for SQL Server 2000
>> athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide
>> quoted text -
>>
>> - Show quoted text -
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

13/12/2007, 01h34	#4
Greg D. Moore \(Strider\) Messages: n/a Hébergeur:	Re: begin and end transaction and transaction log "Kruton" <wmlyerly@gmail.com> wrote in message news:a8d08495-59a1-4090-8906-2a9ff8b01945@o42g2000hsc.googlegroups.com... > Hi Erlang, > This is part of a large OLAP process that runs many times a day. I do > not want to / need to restore to a particular time. I have a dba that > does full backups on a regular basis. I would agree with you to a > certain extent if this were OLTP but it is not. Then your DBA needs to set the DBA to simple recovery. > > Thanks. > > On Dec 12, 2:18 pm, Erland Sommarskog <esq...@sommarskog.se> wrote: >> Kruton (wmlye...@gmail.com) writes: >> > This is more of an architectural question about SQL Server. Can >> > someone please explain why when I perform a query such as the one >> > below that updates a table using begin and end transaction I am unable >> > to programmatically truncate the transaction log. The only way I have >> > found to truncate the transaction log is to stop and start the SQL >> > Server Service. Does this transaction use the tempdb? Is that why I >> > am unable to truncate the transaction log? Is there a better way to >> > do this? >> >> > Begin trans T1 >> >> > Update sometable >> > Set random_row = 'blah' >> >> > End trans T1 >> >> Why would you truncate the transaction log in the first place? >> >> If you run with full recovery and want to be table to restore to a point >> in time, the you should backup your transaction log regularly. >> >> If you don't care about the point-in-time restores but are content with >> restoring from a full backup in case of a failure, you should set the >> database in simple recovery. >> >> -- >> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se >> >> Books Online for SQL Server 2005 >> athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... >> Books Online for SQL Server 2000 >> athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide >> quoted text - >> >> - Show quoted text - > -- Greg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html