March 5, 2004 at 9:01 am
I know people have posted about transaction log physical sizes,
backing them up, shrinking them and all that other stuff...
My question seems to be more related to what I think is strange behavior that I'm hoping someone will clear up for me...
If I take an brand new log file (just attached the database) 1 MB in size and create a simple looping SQL statement that Inserts 10000 records into a single table, the log file physical size remains 1 meg and the usage size is approx 700k.
Now if I generate another loop that deletes those records, or perform a delete table that cleans the whole table, the transaction log explodes to somewhere around 30 Megs in both physical and actual usage size.
Ok...So here's the really funny part if I perform another loop that inserts another 10000 records, the log file shrinks back to under 1 meg in physical and usage size.
Question #1: Why on earth is it doing that?
Question #2: Am I losing the delete information within the log file?
Question #3: If I try to perform a backup and shrink of the logfile after performing the delete on the table, i cannot get the logfile to shrink in physical size. Based on everything that I've read, I expected not to be able to do this, but what on earth is going on with SQL that would shrink the physical size of the log file like I mentioned above that I can't do myself?
March 5, 2004 at 12:22 pm
I have noticed similar behavior from the log, and noticed that when the log reaches 70 percent full it will automatically shrink when I am inserting. I discovered that, even though I set the DB to full recovery mode, SQL treats it like simple recovery, performing automatic checkpoints. After the first full backup is performed, SQL stops the "simple" treatment. I don't know if you are experiencing the same thing, but it certainly sounds like it.
The strange behavior stopped on my DB once I made the first full backup.
Steve
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply