Managing Transaction Logs

Question

Managing Transaction Logs

Viewing 15 posts - 76 through 90 (of 128 total)

You must be logged in to reply to this topic. Login to reply

Mr. Phantomblot SSC Veteran Points: 205 More actions · Answer 1

I'am still a bit confused about the "minimaly logged" operations stuff.

I've tested different recovery modes, performed an "insert into x(id) select id from x" (something I thought was a minimally logged operation), and performed a backup between each switch of recovery modes.

SELECT COUNT(*) from fn_dblog(null, null) shows about the same number for each recovery mode.

My impression was that this should be much less for bulk or simple?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Chris Houghton (7/5/2010)
Am I correct in thinking that a checkpoint flushes the buffer to disk and by disk I mean the transaction log (ldf).

No. Checkpoint forces all dirty data pages to disk and by disk I mean the data file (.mdf, .ndf). Transactions are hardened into the transaction log at the point that the transaction completes, ie when the COMMIT happens

When a user requests another record, presumable this is fetched from the mdf,

Only if the page that contains that record is not already present in the data cache. SQL tries to cache data that's used so that it doesn't incur the cost of the disk IO. If the page is already in memory, the row is simply returned from there, no further trips to the data file necessary.

This may be of interest.

http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/

There's also a fair bit in Books Online, though you may have to dig to find it. Finally, read Paul Randal's blog. http://www.sqlskills.com/blogs/paul/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

stianl72 (7/5/2010)
I'am still a bit confused about the "minimaly logged" operations stuff.

http://msdn.microsoft.com/en-us/library/ms191244%28SQL.90%29.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mr. Phantomblot SSC Veteran Points: 205 More actions · Answer 4

GilaMonster (7/5/2010)
http://msdn.microsoft.com/en-us/library/ms191244%28SQL.90%29.aspx

Ok, so a "normal" INSERT....SELECT * FROM X doesn't cut it for minimally logged?

kromerm SSC Rookie Points: 25 More actions · Answer 5

One of the better treatments on the confusing topic of backups in SQL Server and their impact on the transaction log. I saw your replies in the forum here about checkpoints, which I thought is the only thing missing from your article. But perhaps that is what causes confusion: too many of these stories attempt to put ALL of the cases and too much info into the story and it obfuscates what is most needed. IMO, a solid understanding of the relationship between backups & log and this does it nicely.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

In SQL 2005 (which that link was for), no.

In SQL 2008 and 2008 R2...

Using INSERT INTO…SELECT to Bulk Load Data with Minimal Logging
You can use INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.
Minimal logging for this statement has the following requirements:
* The recovery model of the database is set to simple or bulk-logged.
* The target table is an empty or nonempty heap.
* The target table is not used in replication.
* The TABLOCK hint is specified for the target table.

Quote from Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Houghton SSCrazy Points: 2557 More actions · Answer 7

Much appreciated Gail. The term "to disk" always confused me. I never knew whether it referred to the data file or the log file. Your response has clarified that for me. I will definitely visit the links you provided. Thanks again for taking the time to reply.

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 8

HI Gail,

"Transaction log entries are considered active until the data pages that were modified by that transaction have been written to disk. Once that occurs, the log entries are considered inactive and are no longer necessary for database recovery."

There may be situations where the modified data pages flushed to the disk (data file) by the check point but still the transaction would not have been completed. So the log entries cannot be incative untill it records the commit of transaction as this will be needed for the recovery purpose.

subban

jwilder SSC Veteran Points: 206 More actions · Answer 9

When in Bulk-Logged Recovery mode is everything logged? Is there a chance that certain transaction won't be logged? Or is the only downside that the transaction log backup will take longer?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

All modifications are always logged regardless of recovery model. In bulk logged, some operations can be minimally logged, which means less log impact (log growth), but larger log backups and some limitation on point-in-time restore.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gary Jones Grasshopper Points: 16 More actions · Answer 11

What role does the transaction log play in replication?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Snapshot and merge none. Transactional the log reader reads the changes from the transaction log and writes them through to the distributor

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks Gail - it is nice to have educational material like this.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sameer.kasi200x SSC Veteran Points: 276 More actions · Answer 14

Any suggestions/recommendations on multiplexing for Transaction Logs. Is it of any use (like the multiplexing for Datafiles may help reduce the disk contention and also helps administering the space usage properly and avoids running into no disk all of a sudden).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

No use whatsoever. SQL uses transaction logs serially, it does not stripe log records across log files nor use them in parallel.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass