July 9, 2009 at 2:42 pm
Can you give me any specific examples on "unusual activities"?
I need some clarity on this!!!!!!!
Don't take me wrong my friend, I was just curious.
July 9, 2009 at 3:25 pm
Can anybody help???
July 10, 2009 at 12:41 am
Another scenario to shrink the log is when you don't have sufficient disk space and your log is abnormally huge. But it's not ideal to shrink log often.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
July 10, 2009 at 6:53 am
An example of 'unusual activity' would be reloading a large table.
We normally have about 10,000 rows added to our invoice table in our data warehouse nightly. The table has well over 22 million rows.
Once in awhile, we add new columns - did we ship it on time, flag for early / late, calculate number of days early / late, etc.
Since these records come over from another system, we truncate the table and reload it from the other system.
That creates a lot more activity than normal.
Greg E
July 10, 2009 at 8:20 am
Thanks Greg!
Still am expecting few answers on my below comment
You can't shrink a log that's full because there's no space within the file to release to the OS.
I want to add a point to the above statement.
When i run the indefinte loop which was given above, it is trying to insert data into dirty pages i believe and also inside the T-log and that is the reason why it is throwing Msg 9002 Log Full Error. But when i query the table, i can see only the data which was previously consistent but not the data which i have inserted using the loop. That means it is still an Active transaction which is not yet committed but filled the log. Right???
If that is the case , even after i got the log full Error, but in pratical i tried to insert few records 10 - 50 records into the tables and it is still allowing me to get inserted. what is happening inside my log???????? How it is able allocate space inside my log???
Can you please explain???
July 10, 2009 at 8:34 am
Best thing you can do at this point is open up BOL (Books Online, the SQL Server Help System) and start reading about transaction logs. The following link is based on the local copy of BOL on my desktop system, so hopefully it works for you:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/36d7f140-b46a-4b1b-81a5-a2e2dddf88c6.htm
July 10, 2009 at 9:52 am
Gail also has a couple of articles on her website talking about "log checkpoints/lazywriter" and "deleting logs" that might shed some light on the doubts you appear to be having...
edit: Sorry, should have read the whole thread before chiming in. Gail already gave you this advice (I should've known better 😀 ).
-- You can't be late until you show up.
July 10, 2009 at 10:47 am
Hey Guys,
Dont catch me wrong. I read the article and it is really helpful for me in understanding how the database is going to come ONLINE after recovery phase.
But one thing i got stucked is when the Log is full then it shouldnt allow any more DML operations. But it is allowing me? Here is where am got stucked and am not able get convinced with any of the answers so far.
Anyways Thanks for all the valuable inputs!
July 10, 2009 at 11:58 am
If your log is full, is your disk full too?
What is the growth set to for your log?
What is your recovery model (full, simple)?
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
July 10, 2009 at 12:27 pm
I have given all the steps in 1st Page.
And database is in Full Recovery Mode.
July 10, 2009 at 12:32 pm
mahesh.vsp (7/10/2009)
But one thing i got stucked is when the Log is full then it shouldnt allow any more DML operations.
Bear in mind that not all log entries are the same size.
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
July 10, 2009 at 3:17 pm
What type of situation are you trying to emulate? A disk failure where you have lost the entire ldf file?
July 10, 2009 at 9:25 pm
No, that was already cleared by Gail. I read his article which was really superb.
Next i went for shrinking the log file when i encountered the log full error.
I think the post contains everything in detail. Please go the entire post once.
And Gail suggested not to go for Log Shrinking which is really a bad idea in Real scenarios. That was even good. But my point was when i encounter, the log full error, then go back to my Management studio and insert few records into the table, it is still allowing me. Basically i was running a indefinetly loop provided with a restricted growth on LDF file so that i can see that Msg 9002 Log full error. It has come , but how come it is allowing me to do some INSERTS ehn there is no space at all inside my LDF?
Gail was also saying that, different log entries may take different size inside the log. But i was giving the same old INSERT repeatedly and query getting executed successfully.
Example insert into
July 10, 2009 at 9:26 pm
Here s the INSERT stmt
INSERT INTO T1 SELECT 1
July 11, 2009 at 9:22 am
I think Gail is on the right track about log entry size.
Fill a 1 gallon jug with marbles
Is it full?
Then pour some sand in - more fits in
Is it full yet?
Then fill it with water.....
Your own description is coming to this type of conclusion.
Not sure who is really qualified to answer this.
I'd imagine some who actually wrote the code for the product, which sure isn't me.
Greg E
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply