SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Growing Despite Simple Recovery


Transaction Log Growing Despite Simple Recovery

Author
Message
brainy
brainy
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 649
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
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, 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


brainy
brainy
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 649
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
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, 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


TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31069 Visits: 8669
It is amazing that 9GB of disk space is a problem these days. You can fit that on a USB key now! :-D 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 on googles mail service
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 6240
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! :-D


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... :-P).
aniltkp-875365
aniltkp-875365
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
Please post new questions in a new thread. Thanks

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search