Re: begin and end transaction and transaction log

"Kruton" <wmlyerly@xxxxxxxxx> wrote in message
news:a8d08495-59a1-4090-8906-2a9ff8b01945@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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...@xxxxxxxxxxxxx> wrote:
Kruton (wmlye...@xxxxxxxxx) 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...@xxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: not a valid undo file for database
    ... The UNDO file is created when you perform RESTORE using the STANDBY option. ... This is because SQL Server will actually perform recovery based on the transaction log when you are ...
    (microsoft.public.sqlserver.server)
  • Re: Insert Into Without Log
    ... transaction log. ... For instance a truncate can be rolled back if it is within ... Looking for a SQL Server replication book? ... > "Hilary Cotter" ha scritto nel messaggio ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Restore Transaction log or the .bak
    ... Databases located under Administering SQL Server. ... > If there is inconsistency on the DB and I want to restore to a last known ... should I restore the transaction log files which is backed up ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log
    ... There cannot be users in the DB when you go to do a restore. ... Allan Mitchell (Microsoft SQL Server MVP) ... the job which applies 'Transaction Log' failed with an error ...
    (microsoft.public.sqlserver.security)
  • Re: begin and end transaction and transaction log
    ... found to truncate the transaction log is to stop and start the SQL ... If you run with full recovery and want to be table to restore to a point ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)