[THIN] Re: OT: SQL 2000 Log file
- From: Henry Sieff <hsieff@xxxxxxxxxxxx>
- To: "'thin@xxxxxxxxxxxxx'" <thin@xxxxxxxxxxxxx>
- Date: Mon, 17 Feb 2003 11:11:18 -0600
> -----Original Message-----
> From: Eddy, Tim S. [mailto:eddyts@xxxxxxxxxxxx]
> Sent: Monday, February 17, 2003 10:28 AM
> To: 'thin@xxxxxxxxxxxxx'
> Subject: [THIN] Re: OT: SQL 2000 Log file
>
>
>
> The best way to automatically shrink the Transaction Log is
> to set "Truncate
> Log on Checkpoint". Then when the log is backed up (which
> should be at least
> once a day) it will automatically truncate the log after the
> data has been
> moved into the DB.
No, that is not how it functions at all.
Here's an overview of how SQL server works:
1) user submits transaction
2) change is made to the data inside the SQL Server cache (in RAM, either
physical or virtual) into a "dirty page"
3) transaction is written to transaction log
4) the lazywriter thread writes idle dirty pages to disk
5) after a period of time determined by the recovery interval setting
(defaults to 0 which allows SQL server to determinte) the checkpoint thread
issues a checkpoint, which commits all dirty pages (including non-idle ones)
to disk. The checkpoint also writes all outstanding transactions to the log.
Now, when you truncate your log on checkpoint, it means that after every
checkpoint, your log is flushed at every checkpoint. This renders
transaction log backups useless, because the transaction log now has only
those transactions since the last checkpoint, not all transactions since the
last transaction log backup.
This is acceptable only at smaller environments which do not need to be able
to restore to a point between two full backups. Again, if you set tuncate
log on checkpoint, those logs are useless for recovery purposes.
Henry
>
> -----Original Message-----
> From: Henry Sieff [mailto:hsieff@xxxxxxxxxxxx]
> Sent: Friday, February 14, 2003 6:27 PM
> To: 'thin@xxxxxxxxxxxxx'
> Subject: [THIN] Re: OT: SQL 2000 Log file
>
>
>
> Transaction logs are automatically emptied every time you do
> a transaction
> log backup. However, this won't shrink your transaction log
> file. It simply
> removes the existing entries from them.
>
> If you need to shrink your transaction log file (to recover
> disk space), you
> should:
> a) back it up.
> b) using Query analyzer, do DBCC SHRINKFILE(<name of log file
> in enterprise
> manager>,X) , where X is the target size in MB's you are
> trying to get to.
>
> Generally speaking, if you see alot of growth in transaction
> log files over
> the course of a month, it means that you are not backing up
> your transaction
> logs often enough.
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> shows how to
> shrink transaction logs; I do recommend reviewing your backup
> procedures if
> you see growth in your log files.
>
>
>
> > -----Original Message-----
> > From: Michael Boggan [mailto:MBoggan@xxxxxxxxxxx]
> > Sent: Friday, February 14, 2003 3:47 PM
> > To: THIN (E-mail)
> > Subject: [THIN] OT: SQL 2000 Log file
> >
> >
> > I am not much on SQL. How do I empty the transaction log
> > file? Is there a
> > way to set the maintenance to do it automatically also?
> >
> > Thanks,
> >
> > --------------------------------------------
> > Michael Boggan, Network Eng/Citrix Admin
> > Virtual Desktop Inc.
> > Dallas, Tx
> > ph. (972) 960.6400
> > f. (972) 960.6445
> > email. mboggan@xxxxxxxxxxx
> > --------------------------------------------
> >
> > *********************************************************
> > This Week's Sponsor - RTO Software / TScale
> > TScale increases terminal server capacity.
> > Get 30-40% more users per server to save $$$ and time.
> > Add users now! - not more servers. If you're using Citrix,
> > you must learn about TScale! Free 30-day eval:
> > http://www.rtosoft.com/Enter.asp?ID=79
> > **********************************************************
> >
> > For Archives, to Unsubscribe, Subscribe or
> > set Digest or Vacation mode use the below link:
> > http://thethin.net/citrixlist.cfm
> >
> *********************************************************
> This Week's Sponsor - RTO Software / TScale
> TScale increases terminal server capacity.
> Get 30-40% more users per server to save $$$ and time.
> Add users now! - not more servers. If you're using Citrix,
> you must learn about TScale! Free 30-day eval:
> http://www.rtosoft.com/Enter.asp?ID=79
> **********************************************************
>
> For Archives, to Unsubscribe, Subscribe or
> set Digest or Vacation mode use the below link:
> http://thethin.net/citrixlist.cfm
>
> =============================================================
> This message is intended only for the use of the individual
> or entity to which it is addressed, and may contain
> information that is privileged, confidential and exempt from
> disclosure under applicable law. If you are not the
> intended recipient, you are hereby notified that any use,
> dissemination or copying of this communication is strictly
> prohibited. If you have received this communication in error,
> please notify us immediately by returning the original
> message to the sender and then delete the message.
> Thank you.
> =============================================================
> *********************************************************
> This Week's Sponsor - RTO Software / TScale
> TScale increases terminal server capacity.
> Get 30-40% more users per server to save $$$ and time.
> Add users now! - not more servers. If you're using Citrix,
> you must learn about TScale! Free 30-day eval:
> http://www.rtosoft.com/Enter.asp?ID=79
> **********************************************************
>
> For Archives, to Unsubscribe, Subscribe or
> set Digest or Vacation mode use the below link:
> http://thethin.net/citrixlist.cfm
>
*********************************************************
This Week's Sponsor - RTO Software / TScale
TScale increases terminal server capacity.
Get 30-40% more users per server to save $$$ and time.
Add users now! - not more servers. If you?re using Citrix,
you must learn about TScale! Free 30-day eval:
http://www.rtosoft.com/Enter.asp?ID=79
**********************************************************
For Archives, to Unsubscribe, Subscribe or
set Digest or Vacation mode use the below link:
http://thethin.net/citrixlist.cfm
Other related posts:
