Hello everyone,
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

Thanks!

Kruton (wmlyerly@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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.

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 -

"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

Kruton (wmlyerly@gmail.com) writes:
> 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 you need simple recovery. What I failed to say is that with simple
recovery, SQL Server will regularly truncate the transaction log, and thus
keep it in check. The one thing to keep in mind is that truncation never
goes past the open transaction, so if you have a long-running transaction
the log can grow never the less.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland,
This sounds like it could be it. I will give it a try. Thanks


On Dec 13, 12:21 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Kruton (wmlye...@gmail.com) writes:
> > 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 you need simple recovery. What I failed to say is that with simple
> recovery, SQL Server will regularly truncate the transaction log, and thus
> keep it in check. The one thing to keep in mind is that truncation never
> goes past the open transaction, so if you have a long-running transaction
> the log can grow never the less.
>
> --
> 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