begin and end transaction and transaction log

begin and end transaction and transaction log

Post by Kruto » Thu, 13 Dec 2007 13:26:01


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!
 
 
 

begin and end transaction and transaction log

Post by Erland Som » Fri, 14 Dec 2007 07:18:27

Kruton ( XXXX@XXXXX.COM ) writes:

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, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/

 
 
 

begin and end transaction and transaction log

Post by Kruto » Fri, 14 Dec 2007 07:48:14

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.
 
 
 

begin and end transaction and transaction log

Post by Strid » Fri, 14 Dec 2007 09:34:58


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






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

begin and end transaction and transaction log

Post by Erland Som » Fri, 14 Dec 2007 17:21:01

Kruton ( XXXX@XXXXX.COM ) writes:

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, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

begin and end transaction and transaction log

Post by Kruto » Sat, 15 Dec 2007 01:57:32

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