Log File Filling Up

Hi All,
You may have seen my earlier post about an SQL log file filling up in
the space of 1 hour on a small ASP based website...
http://groups.google.co.uk/group/mi...0d4affac1291ed1
Since this post, I have been in touch with our hosting company with
some questions. Heres the answers:

> - What recovery mode is the database set to?
You database is set ti simple recovery mode.

> - How often is the DB backed up? When?
It is backed up nightly around 11.30pm

> - Is trucate log on check point switched on?
Yes it is turned on.
Given the above, what should I be looking for in my code to prevent the
log file filling up?
Surely if "truncate log on check point" is on, then the log should
never fill up?
Is a 'checkpoint' an explicit or implicit operation? i.e. Do I need to
use specific code following an update/insert/delete to cause a
checkpoint, and therefore a log file truncate?
Should I be using something like this around each operation that causes
a data change?...
BEGIN TRANS
--Some SQL
GO
Also, we do have some user SP's that do alot of updating - Should I be
using locks on these? If I did, would that cause the rest of the site
to stop working?
Any help will be much appreciated.
Simon.The simple answer is to do as little updating in a single transaction as
possible and keep your log size sufficiently large to accommodate your
largest transaction plus a little bit more as a buffer. Don't update a
billion rows in one fell swoop. Don't do a BEGIN TRAN and then run 100's of
INSERT/UPDATE/DELETE statements before you commit.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<adotek@.adotek.co.uk> wrote in message
news:1151945419.675236.308850@.h44g2000cwa.googlegroups.com...
Hi All,
You may have seen my earlier post about an SQL log file filling up in
the space of 1 hour on a small ASP based website...
http://groups.google.co.uk/group/mi...0d4affac1291ed1
Since this post, I have been in touch with our hosting company with
some questions. Heres the answers:

> - What recovery mode is the database set to?
You database is set ti simple recovery mode.

> - How often is the DB backed up? When?
It is backed up nightly around 11.30pm

> - Is trucate log on check point switched on?
Yes it is turned on.
Given the above, what should I be looking for in my code to prevent the
log file filling up?
Surely if "truncate log on check point" is on, then the log should
never fill up?
Is a 'checkpoint' an explicit or implicit operation? i.e. Do I need to
use specific code following an update/insert/delete to cause a
checkpoint, and therefore a log file truncate?
Should I be using something like this around each operation that causes
a data change?...
BEGIN TRANS
--Some SQL
GO
Also, we do have some user SP's that do alot of updating - Should I be
using locks on these? If I did, would that cause the rest of the site
to stop working?
Any help will be much appreciated.
Simon.

log file at sql server shutdown

hi all,
maybe a poor question, but i cannot test it for myself now.
when i shutdown or stop sql server, will there all logfiles be written in
the databasefiles, the same as a backup will do?
thanks for help
jeffHi
It depends on how orderly the shutdown is, in an orderly shutdown a
checkpoint is carried out which will write all dirty pages to disc.
Check out:
rt_6628.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
rt_6628.asp
or
_4hk5.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_4hk5.asp
241g.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
241g.asp
On restarting SQL server will try and recover any committed transactions
from the log file.
John
"j.goines" wrote:

> hi all,
> maybe a poor question, but i cannot test it for myself now.
> when i shutdown or stop sql server, will there all logfiles be written in
> the databasefiles, the same as a backup will do?
> thanks for help
> jeff
>
>|||thanks a lot!
jeff
"John Bell" <jbellnewsposts@.hotmail.com> schrieb im Newsbeitrag
news:2706FFFE-4BD4-42EC-8465-18A7569B8F60@.microsoft.com...[vbcol=seagreen]
> Hi
> It depends on how orderly the shutdown is, in an orderly shutdown a
> checkpoint is carried out which will write all dirty pages to disc.
> Check out:
> tart_6628.asp" target="_blank">http://msdn.microsoft.com/library/d...
tart_6628.asp
> or
> te_4hk5.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
te_4hk5.asp
> o_241g.asp" target="_blank">http://msdn.microsoft.com/library/d... />
o_241g.asp
> On restarting SQL server will try and recover any committed transactions
> from the log file.
> John
> "j.goines" wrote:
>

log file at sql server shutdown

hi all,
maybe a poor question, but i cannot test it for myself now.
when i shutdown or stop sql server, will there all logfiles be written in
the databasefiles, the same as a backup will do?
thanks for help
jeff
Hi
It depends on how orderly the shutdown is, in an orderly shutdown a
checkpoint is carried out which will write all dirty pages to disc.
Check out:
http://msdn.microsoft.com/library/de...start_6628.asp
or
http://msdn.microsoft.com/library/de...reate_4hk5.asp
http://msdn.microsoft.com/library/de...ca-co_241g.asp
On restarting SQL server will try and recover any committed transactions
from the log file.
John
"j.goines" wrote:

> hi all,
> maybe a poor question, but i cannot test it for myself now.
> when i shutdown or stop sql server, will there all logfiles be written in
> the databasefiles, the same as a backup will do?
> thanks for help
> jeff
>
>
|||thanks a lot!
jeff
"John Bell" <jbellnewsposts@.hotmail.com> schrieb im Newsbeitrag
news:2706FFFE-4BD4-42EC-8465-18A7569B8F60@.microsoft.com...[vbcol=seagreen]
> Hi
> It depends on how orderly the shutdown is, in an orderly shutdown a
> checkpoint is carried out which will write all dirty pages to disc.
> Check out:
> http://msdn.microsoft.com/library/de...start_6628.asp
> or
> http://msdn.microsoft.com/library/de...reate_4hk5.asp
> http://msdn.microsoft.com/library/de...ca-co_241g.asp
> On restarting SQL server will try and recover any committed transactions
> from the log file.
> John
> "j.goines" wrote: