February 9, 2012 at 10:01 pm
If I had a single INSERT INTO statement that was very big, what would happen to the transaction log?
What is the biggest INSERT statement that SQL Server 2008 Standard will accept? For example, say I have a 2 GB .txt file. Assuming it was formatted in a way that worked, could I insert it all with a single transaction? Could I roll it back? Would it be given a single LSN? Would the TLOG (.ldf file) be very large too?
February 9, 2012 at 11:00 pm
The log would have to be large enough to accommodate the log records for the insert. If the log was not big enough, it would have to grow. If it couldn't grow the insert would fail and be rolled back.
The biggest insert allowed would be the largest that the log has space to handle, yes you could insert 2GB in a single insert (though if you're loading from files, look up bulk insert or bcp as they can be minimally logged in recovery models other than full). The log would probably need to be around 4GB for that. (rough guess)
Each log record has it's own LSN, a log record would be a singe row of the insert or a modification to one of the allocation pages or similar.
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
February 10, 2012 at 12:11 pm
So a huge insert into would be given a single LSN?
If the database was in a simple recovery mode but the log file couldn't grow, would the transaction fail?
If a TLog is set to a specific size and cannot grow and transactions start failing during peak business hours, is the canonical solution to back up the TLog file then later expand its size?
In general, outside of big inserts, will an uncommitted transaction have an LSN? Will it appear in the TLog?
February 10, 2012 at 12:17 pm
Rowles (2/10/2012)
So a huge insert into would be given a single LSN?
No.
Each log record has it's own LSN, a log record would be a single row of the insert or a modification to one of the allocation pages or similar.
If the database was in a simple recovery mode but the log file couldn't grow, would the transaction fail?
Yes, and that's true in any recovery model. Log records belonging to an active transaction can't be overwritten, nothing dependant on recovery model there.
If a TLog is set to a specific size and cannot grow and transactions start failing during peak business hours, is the canonical solution to back up the TLog file then later expand its size?
The solution is to find out why the log isn't been reused and resolve that specific problem (a log backup is useless if the reason the log is full is an active transaction)
http://qa.sqlservercentral.com/articles/Transaction+Log/72488/
In general, outside of big inserts, will an uncommitted transaction have an LSN? Will it appear in the TLog?
Every single data modification in SQL is logged. A log record (a single log record) gets a unique LSN (a transaction would have at least 2 log records - begin and commit and usually a lot, lot more). Think of an LSN as kinda like the identity column for a log (it's not that simple and it's not an integer)
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
February 10, 2012 at 9:38 pm
LSNs aren't integers?
Say a row with three columns gets all three of its values changed. How many LSNs would be generated if this happened with a single INSERT INTO ... VALUES statement? Three LSNs?
February 11, 2012 at 3:39 am
Rowles (2/10/2012)
LSNs aren't integers?
No.
Say a row with three columns gets all three of its values changed. How many LSNs would be generated if this happened with a single INSERT INTO ... VALUES statement? Three LSNs?
A single insert ... into that inserts a single row? (number of columns is irrelevant) Inserts can't change values, that's what an update does
At its simplest (assuming no allocation pages need changing as a result of the insert
One log entry (with LSN) for the begin xact (begin transaction)
One log entry (with LSN) for the single row been inserted
One log entry (with LSN) for the commit transaction
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
February 11, 2012 at 6:54 am
As a side note, you can decide a batch of records in one transaction in BULK INSERT. So even if you are inserting 1M records and you decide 1K as transaction batch, SQL Server commit or rollback inserts on each 1K records.
More: (search for 'BATCHSIZE', 'ROWS_PER_BATCH' & MAXERRORS')
June 3, 2012 at 7:09 pm
Microsoft says that LSNs are integers assuming Microsoft Press speaks for them. In Microsoft SQL Server 2008: Implementation and Maintenance Self-Paced Training Kit by Michael Hotek and published by Microsoft Press, page 203 says "The LSN is an integer value that starts at 1 when the database is created and increments to infinity."
Does anyone agree or disagree that LSNs are integers? Is the book wrong?
June 4, 2012 at 3:15 am
They're not pure integers, and they don't exactly increment.
An LSN is of the form Virtual log file:Log block:Log record, each of which is a binary number (can't recall offhand how large). It can be converted to an integer for display, or a varchar.
Looks like this:
0000009b:00000018:0032
0000009b:0000002c:0001
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply