Transaction log

[refshah]

Hi folks,

As a novice on SQL Server, I did a default installation of Express 2008. After a while, C: drive got full because the transaction log had unrestricted growth.

First thing I did was to find out how to get some free space back. I discovered shrinking the log file, and it dropped from 15GB to 40MB.

Questions:
a) the Shrink file dialog showed 99% free; why did the file keep growing if it was vastly 'free'?
b) why does it not recycle the free space instead?
c) what happens to the DB when such an event (no room to grow log file) occurs?

Next, I observed the recovery model was 'Simple'.

Question:
d) how does this relate to the transaction log, in terms of purpose, content, growth, maintenance, etc.

Then, I amended the setting to restrict growth to 10GB.

Questions:
e) is this figure an overkill?
f) how do I figure out an optimum value?
g) what will happen when (if?) it reaches 10GB? eg. recycle?
h) how do I inspect what is currently in the file?


Many thanx in advance,
Shah

[NeoPa]

Shah, I very rarely take this approach to a question thread, but in this case it seems to me like it's the best approach for you to suggest you do a search for an article which discusses Transaction Log handling in SQL Server 2008.

Typically, we only allow one question per thread anyway, but as these are all fundamentally about the same subject we can let that slide for now.

I really do feel that you should start by getting out there and finding out the basics. If you find, after doing that, that you still have specific areas of confusion then you can come back and ask a specific question (or more), but each in their own thread. These questions, apart from being multiple, are also very basic I think you're far better off learning the fundamentals first.