Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Transaction Log Growing Despite Simple Recovery Expand / Collapse
Author
Message
Posted Monday, February 21, 2011 12:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:44 AM
Points: 115, Visits: 646
Yes. My assumption was correct.
There is nothing which cant be logged. But most of the people will think bcp will not be logged where as a simple insert command will log, that's why bcp is fast and dont eat disk space while insertion.

But the fact is bcp will do same inserts in batches or chunks so we dont see much usage of disk(logfile) even we are inserting huge data.



Post #1066950
Posted Monday, February 21, 2011 2:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
brainy (2/21/2011)
But most of the people will think bcp will not be logged where as a simple insert command will log, that's why bcp is fast and dont eat disk space while insertion.


I've not seen too many people assume that bcp isn't logged. If fact, if the DB is in full recovery, it'll be logged much like a normal insert

But the fact is bcp will do same inserts in batches or chunks so we dont see much usage of disk(logfile) even we are inserting huge data.


Not necessarily. If the DB is in full or bulk-logged recovery and a log backup doesn't run between the batches, the log can grow. bcp is minimally logged in bulk-logged and simple recovery, not in full recovery.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1066971
Posted Monday, February 21, 2011 3:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:44 AM
Points: 115, Visits: 646
Hi Gail,
Thanks for your comments.

What is the difference between minimally logged and fully logged. As per my knowledge, only the difference is, in minimally logged the the log will be reused after committing the running transaction. Where as in fully logged the it is not the case. It will be reused only after taking full backup.
(This is the main difference).

Correct me if I am wrong.

Post #1066995
Posted Monday, February 21, 2011 4:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
brainy (2/21/2011)
What is the difference between minimally logged and fully logged. As per my knowledge, only the difference is, in minimally logged the the log will be reused after committing the running transaction. Where as in fully logged the it is not the case. It will be reused only after taking full backup.
(This is the main difference).


Completely wrong.
Fully logged and minimally logged operations have nothing at all to do with when the log space is reused.

Certain operations (and check books online for details) can be minimally logged. This means that instead of SQL logging the complete details of the change (eg in a fully logged insert it will write the values of columns into the transaction log), SQL just logs the details of what pages it changed. This can massively reduce the amount of transaction log used.
Minimally logged operations are (with a couple of exceptions) only possible in bulk-logged or simple recovery models. In full recovery, SQL logs the changes fully. Even in those recovery models, only some operations can be minimally logged (bcp, bulk insert, index rebuilds) and only under certain circumstances

The reuse of the log has to do with recovery model, not the type of operation performed. In simple recovery only the log is reused after a checkpoint, in all other recovery models a log backup is required. See today's headline article for more on transaction log reuse.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1067009
Posted Tuesday, February 22, 2011 6:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
It is amazing that 9GB of disk space is a problem these days. You can fit that on a USB key now! Hopefully the batching process works successfully and for a long time and you can still continue to meet your build window.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1067575
Posted Wednesday, February 23, 2011 3:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 1,635, Visits: 5,592
TheSQLGuru (2/22/2011)
It is amazing that 9GB of disk space is a problem these days. You can fit that on a USB key now!


While true, I'm really not sure I'd want to run a database off one! (Oh, and USB keys are usually formatted as FAT32 which limits you to 2Gb files... ).
Post #1068129
Posted Thursday, March 21, 2013 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 4:15 PM
Points: 1, Visits: 1
i experienced some thing similar. how do you explain it.
datafile size: 17 GB
log size: 12GB

objective: migration from 2005 to 2008 by the external vendor on site.
i noticed 2005 version is using 2000 comparability level.
so i changed the compatibility level to 100. run consistency check.
as db in full recovery mode i changed to simple recovery mode.
As i knew they going to drop indexes and rebuild and stats update changes to objects..etc.
i truncated the log and set to 5 GB.

My expectation: because it is in simple recovery and log should not grow much.

actual: it has grown to 48 GB.

i could not find the reason why it has grown that big.

how do you explain this?
Post #1434070
Posted Thursday, March 21, 2013 5:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
Please post new questions in a new thread. Thanks


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1434086
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse