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.