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:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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.sewrote:
Quote:
Originally Posted by
Kruton (wmlye...@.gmail.com) writes:
Quote:
Originally Posted by
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?
>
Quote:
Originally Posted by
Begin trans T1
>
Quote:
Originally Posted by
Update sometable
Set random_row = 'blah'
>
Quote:
Originally Posted by
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.comwrote in message
news:a8d08495-59a1-4090-8906-2a9ff8b01945@.o42g2000hsc.googlegroups.com...
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
>
Thanks.
>
On Dec 12, 2:18 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
>Kruton (wmlye...@.gmail.com) writes:
Quote:
Originally Posted by
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?
>>
Quote:
Originally Posted by
Begin trans T1
>>
Quote:
Originally Posted by
Update sometable
Set random_row = 'blah'
>>
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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.sewrote:
Quote:
Originally Posted by
Kruton (wmlye...@.gmail.com) writes:
Quote:
Originally Posted by
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
No comments:
Post a Comment