begin and end transaction and transaction log

[Kruton]

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!

[Erland Sommarskog]

Kruton (wm******@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, es****@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

[Kruton]

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 <[e-mail]:

Kruton ([e-mail]) 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, [e-mail]

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 D. Moore \(Strider\)]

"Kruton" <wm******@gmail.comwrote in message
news:a8**********************************@o42g2000 hsc.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 <[e-mail]:

>Kruton ([e-mail]) 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, [e-mail]

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

[Erland Sommarskog]

Kruton (wm******@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, es****@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

[Kruton]

Hi Erland,
This sounds like it could be it. I will give it a try. Thanks
On Dec 13, 12:21 am, Erland Sommarskog <[e-mail]:

Kruton ([e-mail]) 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, [e-mail]

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